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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
fbeydoun
Frequent Visitor

Sorting by Measure that Uses Dynamic Format

I have a measure that dynamically changes based on a parameter that the user controls. The user can choose quantity or value from the parameter, and this measure switches to provide the selected value and formats it correctly. This is how the measure looks:

 

[Actual ] =
IF (
ISFILTERED ( 'Choose Comparison Metric'[Metric] ),
SWITCH (
TRUE (),
LASTNONBLANK ( 'Choose Comparison Metric'[Metric], "" ) = "Quantity", format(sum('Details'[Quantity]), "#,##0"),
LASTNONBLANK ( 'Choose Comparison Metric'[Metric], "" ) = "Value", format([This Month Value]/1000000, "$#,##0.0M"),
format(sum('Details'[Quantity]), "#,##0")
),
format(sum('Details'[Quantity]), "#,##0")
)

 

This part works fine, but the issue I am running into is that once this gets displayed in a matrix, Power BI is treating both of these as text and the user won't be able to sort by that column correctly if they click on the column header. I was wondering if there is a way to keep the formatting for display but force the sorting to be based on the actual value in the measure? if not, are there other solutions for this?

2 REPLIES 2
MattAllington
Community Champion
Community Champion

No, you can’t do it. This is why the solution is only half a solution. You can vote for my idea here https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15231165-conditional-formatted-me...



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks for the info Matt! I voted on your idea.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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