Forum Discussion

Mark_Berry's avatar
Mark_Berry
Helper I
2 years ago

Create DAX to filter table.

Hello everyone,

 

I have created a report with a custom slicer which serves the purpose of showing information related to certain measures. 
In this case a status and rating based off the specific KPIs for each result area. Each Result area is on the same report page and my current slicer is developed to switch between each KPI. 

In the source workbook each KPI is score using varied criteria eg some are % whilst some are time range based or staff number based. 


I have been asked to add a table which shows criteria for each specific KPI and for it to change with the current slicer selection 

 

the source table looks as such 

I want to be able to slice between each KPI and show the specific criteria whilst retaining the Rating and Status columns

an example of one of my slicer measures is as such (Please note I have sanitised the DAX)

 Status = if(
HASONEVALUE('Slicer Table'[Value1]),
SWITCH(
VALUES('Slicer Table'[Value1]),
"1.4", SUMX('1.4','1.4 '[Status]),
"1.1", SUMX('1.1 ','1.1 '[Status]),
"1.2 ", SUMX('1.2 ','1.2 '[Status]),
"1.3 ", SUMX('1.3 ','1.3 '[Status])))
 
Thanks
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI Mark_Berry ,

    follow below steps

      • Create a new table in your data model that contains the KPI values and their corresponding criteria. This table should not have any relationships with other tables to keep it disconnected. It might look something like this:

        Create a Criteria Table:

     

    • KPI Criteria KPI Criteria 1.1 Criteria for KPI 1.1 1.2 Criteria for KPI 1.2 1.3 Criteria for KPI 1.3 1.4 Criteria for KPI 1.4

    • Create a DAX Measure for Criteria:

    1. Selected Criteria = VAR SelectedKPI = SELECTEDVALUE('Slicer Table'[Value1]) RETURN CALCULATE( MAX('KPI Criteria'[Criteria]), FILTER('KPI Criteria', 'KPI Criteria'[KPI] = SelectedKPI) )

      This measure will return the criteria corresponding to the selected KPI in the slicer.

    2. Display the Criteria in a Table Visual:

      • In your report, add a table visual.
      • Place the 'Status' measure, 'Rating' measure, and the 'Selected Criteria' measure in the table. Each row will display the criteria for the selected KPI along with the status and rating.
    • Mark_Berry's avatar
      Mark_Berry
      Helper I
      2 years ago

      Thanks Anonymous , coul you please clarify how to create the criteria table?

      I have imported the excel table I showed in my original post and it looks like such 

       or should I expand this table to include the ratings, status and criteria? 

       

      I also have some KPIs which return a text value in the criteria so what considerations should I have WRT these values?