Formatting based on condition DAX

[corange]

Hi Team, 

I have played with my data set so I could manipulate the Matrix view a bit more. 

I have unpivot a table using two columns so now i have got : 

Column "Category" where I have job count and revenue as attribute 

Column "Value" where I have got the count of jobs and revenue falling under one but easily differentiated due to column "category" 

It works like a treat and I can select if I want to see all or a specific attribute now. The only thing is that revenue should be fornatted as $ but not job count. Is there a formula for me to use that will allow based column Category to format my value on conditions and leaving the value numerical? 

Thanks. 

[Mariusz]

Hi @corange 

you can use the convert function

https://docs.microsoft.com/en-us/dax/convert-function-dax

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[corange]

Do you guys know what we will be the trick to add this as a column instead of a measure? 

TEST = IF(SELECTEDVALUE(SummaryOpsBI[Attribute])="Revenue",FORMAT(CALCULATE(SUM(SummaryOpsBI[Value])),"Currency"), FORMAT(CALCULATE(SUM(SummaryOpsBI[Value])),""))

[Greg_Deckler]

FORMAT is going to make those values text, I thought you wanted to keep them numeric?

[corange]

Hi, 

Well when I tried it it didnt change to text, it kept it as numerical:

I have run few check and the table on the right has been created with the formula mentionned above and it is correct. There are two things I dont know how to solve though: 

1)  Add a delimeter "  , " as per the left example 

2) When I drag and drop my measure into my matrix which as Month & Period in Drill down, the entire calendar I use for my model appears. When I select date range, only data for the date range appear but the rest of the month doesnt dissapear as it will normally do. 

I hope I am making sense for point 2. 

Thank you

I hope the second point make sense. 

[Greg_Deckler]

It is text, you can tell because it is left justified versus right justified. See your images.

[corange]

Hi @Greg_Deckler , 

Thanks for pointing this out. I see it now. 

Any idea how I would be able to use CONVERT() and obtain the same results? How write something around those lines using CONVERT(): 

IF ATTRIBUTE = REVENUE THEN VALUE = CURRENCY, ELSE VALUE = "" meaning not symbol. 

Thank you 

[Greg_Deckler · Accepted solution]

Seems like it should be along the lines of:

Column = IF([Attribute] = "REVENUE",CONVERT([Value,CURRENCY),[Value])

[Greg_Deckler]

@Mariusz - Good call, I must have missed the introduction of that DAX function!

[Mariusz]

Hi @Greg_Deckler 

It's been introduced only a few months back, but it's solving the problems we all had with FORMAT in this king of scenarios.

Thanks 

Mariusz

[corange]

Hi, 

I dont get it. I dont have an issue with FORMAT in that formula and my results are adding and not being transform as Text. 

If I were to use your solution, how will it be written in a DAX Formula? The formula needs to be based on one column where we know if it is either a revenue or a job count, then use another column where I store the value and determine if Column 1 says Revenue then $ else Job Count "". 

Thanks. 

[Greg_Deckler]

Ooo, been round and round on this one. Last time I went down that route it was a hard no.

[corange]

Hi @Greg_Deckler , 

i have found this one and it does work. However, I dont know how to make it respond to the other filter I have got in slicer. For example when I select data range March to April and drop this measure in, it will expand and show me for the entire FY I have got in store. 

Any idea?