Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jtooke
Helper I
Helper I

Sorting Multiple Text-Based Measures

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!

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

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!

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

Exactly what I proposed 👍



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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!

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

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
Super User
Super User

@jtooke How you are visualizing these KPIs?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.