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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.