Creating a slicer/filter with the calculated values from two columns

DAX DAX Calculations
3

good morning @Vsb79 , as you did not provide a PBIX file (which is very useful even in cases like this) - I have made a few assumptions about your report:

  • you followed best practices and are using measures for all of the values used in your report (not just pulling in the column)
  • the two variance columns (Local Sales - National Sales, National Sales - International Sales) were created with measures and are not loaded into your data model.
  • You are using a disconnected table for your filter statement.

Now, with all of these in place, here is my recommended solution:
I have created measures for each of the values in the report, and hidden the original columns so they don’t get used accidentally.
I have created a “Selected Status” measure to give me the value selected by the slicer (if nothing is selected, then “No difference” is the default)

Selected Status = 
SELECTEDVALUE( 'Account Status'[Status], "No difference" )

I have created a Status Toggle measure to used to determine which values will show on your final report table:

Status Toggle = 
VAR _LocalNational = [Local Sls - National Sls]
VAR _NationalInternational = [National Sales - International Sales]
VAR _NoDifference = _LocalNational + _NationalInternational
RETURN
    SWITCH( TRUE(),
        [Selected Status] = "LocalSales<NationalSales" && _LocalNational < 0, 1,
        [Selected Status] = "NationalSales<InternationalSales" && _NationalInternational < 0, 1,
        [Selected Status] = "NationalSales>InternationalSales" && _NationalInternational > 0, 1,
        [Selected Status] = "No difference" && _NoDifference = 0, 1,
        0 )

And finally - I have added that to a table with the toggle switch used as a filter on the visual (but not showing on the table)

image
image872×429 22.2 KB

eDNA Solution - Disconnected Table for Slicer.pbix (23.2 KB)

3 Likes