Forum Discussion
Conditional formatting with multiple decimals
The software version number in our dataset has multiple decimals, and Power BI does not recognize these version numbers as numbers for conditional formatting:
How can we get it to register as a number?
(Also, can we delete the "and" clause?)
Sample data:
| Company | Computer | Version |
| Yipee | BACONATOR | 2.0.1570 |
| Tundra | ALOHA | 2.0.1560 |
| Tundra | GRANDPA | 2.0.1570 |
| Yipee | NETHERLANDS | 2.0.1540 |
Hello Anonymous
I dont think there is a way to make a record with numbers and 2 decimals to be recognised as number in PowerBI.
I would suggest you check if there is a pattern on software versions. e.g. if you have software version in this format:
2.0.176
3.1.033
2.1.001
Simply delete the second decimal, convert it to number and then apply a conditional formatting
You can try the following formula:
Column = FORMAT(REPLACE(Sheet1[Version],4,1,""), "0.0000")
4 Replies
Hello Anonymous
I dont think there is a way to make a record with numbers and 2 decimals to be recognised as number in PowerBI.
I would suggest you check if there is a pattern on software versions. e.g. if you have software version in this format:
2.0.176
3.1.033
2.1.001
Simply delete the second decimal, convert it to number and then apply a conditional formatting
You can try the following formula:
Column = FORMAT(REPLACE(Sheet1[Version],4,1,""), "0.0000")Thank you for the very quick reply 😀 I believe your solution would be an acceptable compromise if it worked for me. I applied your suggested new column using DAX. Unfortunately, the conditional formatting does not work at all as expected:
- "If is blank" does nothing.
- "If is greather than or equal to 2.11570 and less than 99999" does nothing.
- "If is greater than 0 and less than 2.11570" marks EVERY cell.🤔
Did the way we did the FORMAT function change the value format to something that isn't fully compatible with the conditional formatting I'm trying to apply?
Sample data:
Company Computer Version Yipee BACONATOR 2.1.1570 Tundra ALOHA 2.1.1560 Tundra GRANDPA 2.1.1570 Yipee NETHERLANDS 2.0.1540 Mattress SKY EDIT: It seems it's treating the value of every cell to be "1"
Hello Anonymous ,
I suggest you check couple of things.
1. When you click on the new field what is the format (datatype) that is shown in PowerBI (Under Measure Tools section on the menu bar? Make sure it is Decimal Number format
2. I think the way you probably used the conditional formatting is wrong. You use the count of the field (count of Cylance version) and not the actual field values. Please change it and put the field name