Forum Discussion
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...
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:
- 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:
- 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:
- 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.
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.
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?