Convert values into whole numbers and decimal based on case

[Anonymous]

Hi,

I have a two columns within the dataset.

One is Attribute and second value

Attribute  Value

Volume   0

Volume   5

Speed     0.05

Speed     2.93

Above is how the data is coming. But when I pull it in a table Volume becomes 0.00 and 5.00

Apparently I want to see whole number when the attribute is volume and decimals when the attribute is speed.

But it shows 0.00 for volume instead of 0 .

Tried a case statement with ROUND for one and not otherwise but its not working.

Any suggestions?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Create a column using below dax expression:

Column = IF('Table'[Attribute] ="Volume",FORMAT('Table'[  Value],"general number"),IF('Table'[Attribute] ="Speed",FORMAT('Table'[  Value],"fixed"),BLANK()))

 And you will see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[parry2k]

@Anonymous you can change the data type to whole number, select column and there will be column tools option in the menu, and change the type to whole number.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

Thank you for your reply. That will not work in my case because I do not want speed in whole number. Speed should be decimal and volume should be whole even though they are both coming from the same column.

[parry2k]

@Anonymous you cannot have different formats for the same column and until you create a column or measure and do conditional formatting and that will your value to the text.

something like this but the new column will be a text column

New Column = 
SWITCH ( TRUE(),
Table[Column] IN { "Weight", "Speed" } , FORMAT( Table[Value], "####0.00" ),
Table[Column] IN { "Heigh"} , FORMAT( Table[Value], "####0" )
)

, 

One other way is to use calculation groups but it is a bit complex but can surely used and you can create a calculation group outside the power bi file using Tabular Editor and it is not officially supported though.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡