Sorting Multiple Text-Based Measures

[jtooke]

Hi,

I have a series of measures that form KRI's calculated from my dataset. Each KRI is associated with a threshold, where if the threshold is not met, the measure returns blank. If the threshold is met, the measure returns with a text string including some calculations from the data.

I am looking to find a way to visualize all of the measures where the KRI threshold has been met (i.e., the measure does not return blank) in a single visualization, and exclude the blank measures. The visualization required would be similar to a text box with bulleted out strings for each KRI. 

Is anyone aware of a method to exclude blank measures from a visualization entirely / dynamically exclude visualizations based on the output? A dynamic sort to push blank measures to the bottom of a visualization would also likely work.

Thanks!

[parry2k · Accepted solution]

@jtooke I don't think what you are trying to achieve is going to work with card visual - it needs some disconnected tables and you will need to use table/matrix visual

[parry2k]

Exactly what I proposed 👍

[parry2k · Accepted solution]

@jtooke I don't think what you are trying to achieve is going to work with card visual - it needs some disconnected tables and you will need to use table/matrix visual

[jtooke · Accepted solution]

Okay, thank you for the reply! I was able to find a workaround solution. I created a new table with a column "KRI" with values 1 through 8 (I have 8 KRI's). In this table I used a switch measure titled Indicator to call the measure values according to their priority rank in the KRI table. 

In the measure DAX, instead of resulting in blank I changed the output to 0 if the criteria is not being met. I then input the KRI and Indicator columns into a table, and put a filter on the table to where Indicator does not equal 0, and then sorted based on the KRI priority.

Thanks again!

[Anonymous]

Hello jtooke,

I didn't fully understand how you did it.

Can you show how you new table looks like?

How you related the measure with the new table? 

And how the measure would look like?

I have a simillar problem with a multiple row cards.

Which i have to change the measures according with the selected month.

Thanks

[jtooke]

Hi EmanuelVBT,

To solve this problem, I entered a table with one column (called "a"), and values in each row corresponding to the number of measures needed. I have 20 measures so I entered values from 1 through 20. I used numbers in this column because I wanted to set a priority ranking on the KRI's for importance, but this column can include whatever you want (i.e., KRI names, etc.).

I then added a measure off of this table with the below formula. The numbers 1-20 correspond to the rows in my table, and the measure is the associated measure to that row. I then visualized with a matrix. Wherever the measure came back 0, the row is filtered out from the matrix and the rows are sorted based on the priority column.

Indicators = SWITCH(MAX('KRI'[a]),

1,[Group1_Measure1],

2,[Group1_Measure2],

3,[Group1_Measure3],

4,[Group1_Measure4],

5,[Group1_Measure5],

6,[Group1_Measure6],

7,[Group1_Measure7],

8,[Group1_Measure8],

9,[Group2_Measure1],

10,[Group2_Measure2],

11,[Group2_Measure3],

12,[Group2_Measure4],

13,[Group2_Measure5],

14,[Group2_Measure6],

15,[Group3_Measure1],

16,[Group3_Measure2],

17,[Group3_Measure3],

18,[Group3_Measure4],

19,[Group3_Measure5],

20,[Group3_Measure6])

I also added to this table another field which is used as a slicer on the page. I wanted to have a user only be able to view measures by Group 1, Group 2, or Group 3, so I added a column to assign those values based on the priority column and added that field to a single-select slicer.

Hope that helps!

[parry2k]

@jtooke How you are visualizing these KPIs?

[jtooke]

I am using the Card (new) visualization with field parameters based on the type of KRI.