Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am not sure how to change the sort order based a measure selection.
I have a matrix table showing measure based on the selection from a slicer. My problem is with the sorting order based on selection.
Example :
Slicer Matrix Table
ER Accounts Total
CM a 100
TC b 40
TR c 90
When I select "ER" or "TR" from slicer, i want the table to sort the measure in Descending order and on "CM" and "TC" selection, the sorting order should change to Ascending order
Please advise how can i achieve it.
Thanks
Shinu
Hi @Shinu1
Try below, I don't know your table names, "tbl_Processed_Rawdata" you can try "ALL" in below dax, basically we want to clear the slicer selection while calculating the rank, ranks should be based on all the KPIs, so adjust below measures or provide me the snapshot of sample data, hope this makes sense.
Rank Measure =
VAR selected =
SELECTEDVALUE ( tbl_KPI[KPI] )
VAR rankER = RANKX(All(tbl_Processed_Rawdata),[ExternalRevenue],,DESC)
VAR rankCM = RANKX(All(tbl_Processed_Rawdata),[Contribution Margin],,ASC)
VAR rankTC = RANKX(All(tbl_Processed_Rawdata),[Total Cost],,ASC)
VAR rankTR = RANKX(All(tbl_Processed_Rawdata),[Total Revenue],,DESC)
RETURN
SWITCH ( selected, "ER", rankER, "CM", rankCM, "TC", rankTC, "TR", rankTR )
@Shinu1 I'm afraid that you can change the sort order based on the selection. What is the use case that you need to change the sort order, maybe there is another solution.
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.
Different measures are looked with different perspective.
Example: The Cost line items are all in negative and hence the highest negative amount needs to pulled at the top in matrix table and viceversa for Reveue/Profit.
Hope I'm clear
Thanks
Shinu
@Shinu1 I didn't see any negative value in your sample dataset and that's why it was not clear.
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.
@Shinu1 only solution I can think of is to create calculated table and use sorting option.
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.
Based on Slicer selection, different measures populate data in the table. One measure might populate positive amount and the other measure might populate negative value. I'm using Switch formula to check slicer selection and then show the respective measures data.
Switch formula:
mKPI = SWITCH(ALLSELECTED(tbl_KPI[KPI]),"ER",[ExternalRevenue],"TR",[Total Revenue],"TC",[Total Cost],"CM",[Contribution Margin],0)
Below are two of the measures used in above Switch statement measure
ExternalRevenue= CALCULATE(SUM(tbl_Processed_Rawdata[External Revenue]))
Total Cost = CALCULATE(SUM(tbl_Processed_Rawdata[Total Cost]))
I need to sort External Revenue in Descending order(Largest to smallest) and Total Cost in Ascending order (Smallest to lowest)
I 'm not sure how to fit in sort parameter in Calculatedtable to see the output in matrix table. If you could give me a small example to fit in the sort parameter, i can give a try to it.
Thanks
Shinu
Hi @Shinu1,
Currently, you can't directly use a slicer to affect with matrix fields sort order. For indirect methods, I'd like to suggest you write a measure with multiple ranking expressions, then you can nested them with if statement condition with slicer selections.
After you add this field into your visual and setup sort by measure value, it can dynamic changes based on slicer selections.
Rank Measure =
VAR selected =
SELECTEDVALUE ( tbl_KPI[KPI] )
VAR rankER = 'rank formula based on value and its sort order type'
VAR rankCM = 'rank formula based on value and its sort order type'
VAR rankTC = 'rank formula based on value and its sort order type'
VAR rankTR = 'rank formula based on value and its sort order type'
RETURN
SWITCH ( selected, "ER", rankER, "CM", rankCM, "TC", rankTC, "TR", rankTR )
Regards,
Xiaoxin Sheng
Thanks for the reply. I tried as you instructed. Below is the screenshot fo the same. The matrix table now has two columns for each header in Columns section and the rank measure is ranking everything as 1.
Below is the Rank Measure:
Hi,
let me know if anyone has any solution for the same.
Thanks
Shinu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
78 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
84 | |
70 |