cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

DAX Conditional Number formatting

Hello there,

 

Not sure if this is possible, but I would like to create a DAX measure that toggles between Currency and Whole Number.

i.e. the user can use a slider to switch between viewing Unit sales to Value sales, I through the below would work, but it doesnt.

I have the slicer control working fine:

View Meansure = if(COUNTROWS(ViewValue)>1,[Total Units],IF(LASTNONBLANK(ViewValue[ViewValueType],0)="Units", [Total Units],CURRENCY([Total Value])))
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: DAX Conditional Number formatting

My understanding is that if you want conditional formatting, you are stuck with Strings.  If you need to make numeric operations, keep the source information different to the display information.  I.e. you have a Calc measure that does all of the work, and can be use by other measures further down your chain.  Then have a Display measure that is simply the conditional format of your Calc.

View solution in original post

5 REPLIES 5
Highlighted
Continued Contributor
Continued Contributor

Re: DAX Conditional Number formatting

Hi @MarkCBB

If you want to show the amount with a leading $ sign, you have to replace the last part of your formula:

 

 

replace 
,CURRENCY([Total Value])
with
,FORMAT([AOV2],"$ #.##")

 

 

Greetings Hp Pfister
Power Query Buch online auf http://www.powerbi-pro.com
Highlighted
Community Support
Community Support

Re: DAX Conditional Number formatting

Hi @MarkCBB,

 

You can create a view table to store the select item, then write a measure switch the display item based on slicer.

 

Table formula:

 

View Table = UNION(ROW("Name","Currency"),ROW("Name","Whole Number")) 

Capture.PNG

 

 

Measure:

View Measure = 
var selector=IF(HASONEVALUE('View Table'[Name]),VALUES('View Table'[Name]),BLANK()) 
return
SWITCH(selector,"Currency",[Total Units],"Whole Number",CURRENCY([Total Units]))

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper V
Helper V

Re: DAX Conditional Number formatting

Hi @Pfister,

 

I have tried to do that, but when that is in a chart visual the chart does not know how to display it as it changes the data type into string.

 

I also tried to wrap the Format in VALUE but Value was not able to convert the String into a number.

 

Any ideas?

Highlighted
Community Champion
Community Champion

Re: DAX Conditional Number formatting

My understanding is that if you want conditional formatting, you are stuck with Strings.  If you need to make numeric operations, keep the source information different to the display information.  I.e. you have a Calc measure that does all of the work, and can be use by other measures further down your chain.  Then have a Display measure that is simply the conditional format of your Calc.

View solution in original post

Highlighted
Helper V
Helper V

Re: DAX Conditional Number formatting

Hi @Ross73312

 

Fair enough I had a feeling it was not going to be possible, I think the best thing I can do in this scenario is to create a "Banner" (Text Card), that says "You are viewing Units" and use SWITCH to change to "You are viewing Value" - This I can do.

 

Regards,

Mark Blackburn

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors