Forum Discussion

kh3ldar's avatar
kh3ldar
Copper Contributor
May 07, 2023
Solved

Pivot, Dax Formula and Slicer

Hello,

I'm new to DAX functions.

I need to create a measure that is based on the Selected Value from a slicer.

This is my formula:

=MAXX(FILTER(RawP, RawP[Month]=SELECTEDVALUE(Slicer_Month)), RawP[Ex SS])


However, it is not able to resolve the slicer. The table is added to data model. I tried to consult with ChatGPT however, it's referencing old info that is currently non-existent. I'm pretty sure the slicer is connected to the pivot and its name is valid and reads so in settings.

What am I doing wrong?

I'd really appreciate your assistance.




excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    May 08, 2023

    kh3ldar 

    SELECTEDVALUE() was introduced for the Excel data model recently, I guess it's still on insider channels only. 

    Alternatively you may use

    myMeasuer:= MAXX (
    FILTER (
        RawP,
        RawP[Month] = IF ( HASONEVALUE ( RawP[Month] ), VALUES ( RawP[Month] ) )
    ),
    RawP[Ex SS]
)

2 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 08, 2023

    kh3ldar 

    SELECTEDVALUE() was introduced for the Excel data model recently, I guess it's still on insider channels only. 

    Alternatively you may use

    myMeasuer:= MAXX (
    FILTER (
        RawP,
        RawP[Month] = IF ( HASONEVALUE ( RawP[Month] ), VALUES ( RawP[Month] ) )
    ),
    RawP[Ex SS]
)
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 08, 2023

    kh3ldar 

    My knowledge of DAX functions is very limited :), to say the least, but at first glance it could be the problem with selectvalue.

     If you want your measure to return a result only when a single value is selected in the slicer, then SELECTEDVALUE would be the better choice. However, if you want your measure to return a result based on multiple selected values in the slicer, then ALLSELECTED would be the better choice.

    In your original formula, you were using SELECTEDVALUE, which would only return a result if a single month was selected in the Slicer_Month slicer.

    If this is the behavior you want, then you should continue to use SELECTEDVALUE.

    However, if you want your measure to return a result based on multiple selected months in the slicer, then you should use ALLSELECTED instead.

     

    Here’s an example (with AI helps 🙂 using the ALLSELECTED function with your data in the formula:

    =MAXX(FILTER(RawP, RawP[Month] IN ALLSELECTED(Slicer_Month)), RawP[Ex SS])

    This formula will return the maximum value of the Ex SS column in the RawP table for all the selected months in the Slicer_Month slicer. 

     

    Hope I was able to help you with this info…if not please ignore the message.

     

    NikolinoDE

    I know I don't know anything (Socrates)

     

Resources