Forum Discussion

magji's avatar
magji
Helper I
3 years ago

DAX - Change filter columns based on a condition.

How can I put dynamic filters in a measure? e.g 

If "Level" is selected in a slicer, then use below calculation, but if "Gender" is selected on a slicer replace LEVEL column in ALLEXCEPT() with GENDER

 

CALCULATE(
    PERCENTILE.INC(DATA[SALARY], 0.1),
         ALLEXCEPT (
                            DATA,
                            DATA[COMPANY_NAME],
                            DATA[LEVEL]
                        )
)

 

 

The measure that I am using is much more complicated, so I can not use different calculations based on Selected Value in the slicer due to performance issues.

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi magji ,

     

    Please try:

    Measure =
VAR SelectedFilter =
    SELECTEDVALUE ( YourSlicerTable[ColumnName] )
RETURN
    SWITCH (
        SelectedFilter,
        "Level",
            CALCULATE (
                PERCENTILE.INC ( DATA[SALARY], 0.1 ),
                ALLEXCEPT ( DATA, DATA[COMPANY_NAME], DATA[LEVEL] )
            ),
        "Gender",
            CALCULATE (
                PERCENTILE.INC ( DATA[SALARY], 0.1 ),
                ALLEXCEPT ( DATA, DATA[COMPANY_NAME], DATA[GENDER] )
            )
    )

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum