Forum Discussion

MichaelHalling's avatar
MichaelHalling
Frequent Visitor
2 years ago
Solved

Using Slicer as input for a DAX measure ignoring some visual filters

Hi

I am trying to replicate with DAX what I can do in Excel. Basically, I need a measure that can lookup some values from a Table based on the hard coded value "Xccy" in the Base Column and a dynamic value provided by a Slicer (retrieved in DAX by 

SELECTEDVALUE(Selector[CCY])). I want the visual filter for the column "Period" to still be active but ignoring columns "CCY" and "Base" in the Visual. I.e. depending what I choose from the slicer the measure returns differtent values in real time and changes as presented in the tables below. Possible? Help much appreciated.  

 

 

  • 123abc's avatar
    123abc
    2 years ago

    Certainly! It seems like you want to create a DAX measure that looks up values from a table based on a hardcoded value "Xccy" in the Base Column and a dynamic value from a slicer for the CCY column, while still considering the visual filter on the Period column.

    Assuming you have a table named "YourTable" and the columns are "Base," "CCY," and "Period," and you want to create a measure that looks up values based on "Xccy" in the Base column and the dynamic CCY from the slicer, you can use the following DAX formula:

     

    YourMeasure =
    CALCULATE (
    SUM ( YourTable[YourValueColumn] ),
    YourTable[Base] = "Xccy",
    YourTable[CCY] = SELECTEDVALUE ( Selector[CCY] )
    )

     

    Replace "YourValueColumn" with the actual column that you want to aggregate or perform calculations on.

    This DAX measure uses the CALCULATE function to modify the filter context. It filters the table to include only rows where the Base column is "Xccy" and the CCY column matches the selected value from the slicer. The visual filter on the Period column is still considered.

    Make sure to adjust the column names and table names according to your actual data model.

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

1 Reply

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Certainly! It seems like you want to create a DAX measure that looks up values from a table based on a hardcoded value "Xccy" in the Base Column and a dynamic value from a slicer for the CCY column, while still considering the visual filter on the Period column.

    Assuming you have a table named "YourTable" and the columns are "Base," "CCY," and "Period," and you want to create a measure that looks up values based on "Xccy" in the Base column and the dynamic CCY from the slicer, you can use the following DAX formula:

     

    YourMeasure =
    CALCULATE (
    SUM ( YourTable[YourValueColumn] ),
    YourTable[Base] = "Xccy",
    YourTable[CCY] = SELECTEDVALUE ( Selector[CCY] )
    )

     

    Replace "YourValueColumn" with the actual column that you want to aggregate or perform calculations on.

    This DAX measure uses the CALCULATE function to modify the filter context. It filters the table to include only rows where the Base column is "Xccy" and the CCY column matches the selected value from the slicer. The visual filter on the Period column is still considered.

    Make sure to adjust the column names and table names according to your actual data model.

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.