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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Stoppelaar
Frequent Visitor

DAX Formatting inside a Measure?

Hi,

I have some Measures, sales, Prices, Share in different measures, to make some of my reports more flexible would I like to have all my measures in one slicer.

 

So what I have done is to create a table "FilterMeasure" with the measure and then I use below DAX that connects me to the Measure.

 

Sales =
IF(
HASONEVALUE(Products[SHORT]),
SWITCH(
TRUE(),
VALUES(FilterMeasures[Measure]) = "Value Sales'000", [Value Sales'000],
VALUES(FilterMeasures[Measure]) = "Volume Sales'000", [Volume Sales'000],
VALUES(FilterMeasures[Measure]) = "Units Sales'000", [Units Sales'000],
VALUES(FilterMeasures[Measure]) = "Price/EAN", [Price/EAN],
VALUES(FilterMeasures[Measure]) = "Price/KG", [Price/KG],
VALUES(FilterMeasures[Measure]) = "Value%Share", [Values MS],
VALUES(FilterMeasures[Measure]) = "Volume%Share", [Volume MS],
VALUES(FilterMeasures[Measure]) = "Unit%Share", [Unit MS],
BLANK()
),
CALCULATE(
SWITCH(
TRUE(),
VALUES(FilterMeasures[Measure]) = "Value Sales'000", [Value Sales'000],
VALUES(FilterMeasures[Measure]) = "Volume Sales'000", [Volume Sales'000],
VALUES(FilterMeasures[Measure]) = "Units Sales'000", [Units Sales'000],
VALUES(FilterMeasures[Measure]) = "Price/EAN", [Price/EAN],
VALUES(FilterMeasures[Measure]) = "Price/KG", [Price/KG],
VALUES(FilterMeasures[Measure]) = "Value%Share", [Values MS],
VALUES(FilterMeasures[Measure]) = "Volume%Share", [Volume MS],
VALUES(FilterMeasures[Measure]) = "Unit%Share", [Unit MS],
BLANK()
)
,ALL(Products[SHORT]))
)
 
My issue now is that they all are formated in the same way, if I use "General" formatting on then they all have 2 decimals and I would like to to have wholenumber for Sales, 2 decimals for Price and Share needs to be in % with 1 decimals...
 
Is this possible? I have google a lot and look in this forum but I cant really find a way to use DAX to format the different measures, is there anyone who know if or how it can be done? 
 
Many thanks for you help!
Andreas
 
1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

Format the measure as a whole number, but in the price and % values follow this pattern:

VALUES(FilterMeasures[Measure]) = "Price/EAN", FORMAT[Price/EAN], "#,##0.00"),

 

and

VALUES(FilterMeasures[Measure]) = "Value%Share", FORMAT([Values MS], "#0.0%")





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
PaulDBrown
Community Champion
Community Champion

Format the measure as a whole number, but in the price and % values follow this pattern:

VALUES(FilterMeasures[Measure]) = "Price/EAN", FORMAT[Price/EAN], "#,##0.00"),

 

and

VALUES(FilterMeasures[Measure]) = "Value%Share", FORMAT([Values MS], "#0.0%")





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Many thanks @PaulDBrown! This give me the format I want on all the Measure!

Now next challange is sorting, because it works perfect on Val/Vol/Uni but when sorting on Price and Share it comes in nummeric order from 1to9, I have tested a bit but cant really find a solution that the highest/lowest comes first, do you know anything about this?   

The reason that the sorting is as you see it is because FORMAT returns a text string. You might try adding the numerical value as a new measure to sort the visual by. You can then hide it by turning off the text wrap options in the formatting pane and "hiding" the column by dragging the right boundary left to conceal it.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi @Stoppelaar ,

 

you can achieve something like this with Calculation Groups.

 

https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors