Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

SELECTED VALUE Filters

Hi All,

I have the below syntax. The 'Dimension'[Dimension Fields] is a field parameter. I want to filter based on the field dimension and return the calculation from the fields.

 

SWITCH TOP10 = SWITCH(
TRUE(),
SELECTEDVALUE('Dimension'[Dimension Fields]) = "Orange", CALCULATE([Top 10 - Orange]),
SELECTEDVALUE('Dimension'[Dimension Fields]) = "Apple", CALCULATE([Top 10 - Apple]),
SELECTEDVALUE('Dimension'[Dimension Fields]) = "Grapes", CALCULATE([Top 10 - Grapes]),
SELECTEDVALUE('Dimension'[Dimension Fields]) = "Pineapple", CALCULATE([Top 10 - Pineapple]),
SELECTEDVALUE('Dimension'[Dimension Fields]) = "Avocado", CALCULATE([Top 10 - Avocado])
)


Top 10 - Orange =
CALCULATE( [UnitsVolume],
TOPN( 10, ALL(Fruits[Orange]),[UnitsVolume], DESC ),
VALUES(Fruits[Orange]))


For some reason the SWITCH logic does not work as expected and I get a blank report. The "Top 10 - Orange" calculation works fine when used separately without the SWITCH clause.

Can you please correct the DAX syntax so I can calculate the measures based on the dimension selection?

Thanks

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Anonymous - Small adjustment in SWITCH function to properly handle the selection of dimension fields and return the corresponding calculation

     

    SWITCH TOP10 =
    SWITCH(
    SELECTEDVALUE('Dimension'[Dimension Fields]),
    "Orange", CALCULATE([Top 10 - Orange]),
    "Apple", CALCULATE([Top 10 - Apple]),
    "Grapes", CALCULATE([Top 10 - Grapes]),
    "Pineapple", CALCULATE([Top 10 - Pineapple]),
    "Avocado", CALCULATE([Top 10 - Avocado]),
    BLANK() -- Default value if none of the conditions are met
    )

     

    Try the above and let know

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    rajendraongole1  Thank you very much but I see this

     

     

    I just tried with one selection criteria

     

    SWITCH(
    SELECTEDVALUE('Dimension'[Dimension Fields]),

    "Orange", CALCULATE([Top 10 - Orange]),

    BLANK()

    )

     

    The CALCULATE([Top 10 - Orange]) calculation is as below

    CALCULATE( [Sales],TOPN( 10, ALL('Orange'),[Sales], DESC ),VALUES('Orange))

     

    • rajendraongole1's avatar
      rajendraongole1
      Super User
      2 years ago

      Hi Anonymous 

      Ensure that the 'Dimension' table and the measures referenced in the SWITCH function are properly defined in your Power BI data model.

      This DAX expression checks the selected value of the 'Dimension Fields' column and returns the corresponding calculation based on the selected dimension.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        rajendraongole1  Thank you..I checked the field mappings are correct

         

        However, not sure why I see the composite key issue. Thanks