Conditional formatting with multiple decimals

[Anonymous]

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

[themistoklis · Accepted solution]

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")

[Anonymous]

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"

[themistoklis]

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

[Anonymous]

You were right-- The column was in "Text" format.

To change the column to decimal, I first had to use DAX to change columns with "#N/A" or "null" values to 0:

Yes, you are correct: My goal is to apply conditional formatting based on the value of the cell, but the problem is, if you select to perform conditional formatting based on value, you must select a different column to apply the conditional formatting from (the column you're working on is greyed out):

There is also no "Value" option in the "Rules" category:

All of this is actually very perplexing to me: Isn't one of the primary uses of conditional formatting to format cells based on their value? Am I missing something?

My "workaround" to this for past conditional formatting was to create a new column that contains the hex codes for my preferred background color as demonstrated here: https://community.powerbi.com/t5/Desktop/Calculated-Column-with-multiple-IF-Else-Then/m-p/321730/hig... (and then select my HEX-code column when I go to apply conditional formatting by value for my original column in the GUI). But it seems a little silly to need to create an entirely new column with my background colors when I feel the GUI should have the built-in ability to apply conditional formatting based on individual cell value. Ah well, I suppose that is what I must do for now, thank you.