Conditional formatting works badly

[Anonymous]

Hello,

I am in trouble with conditional formatting with a DirectQuery dataset imported from SQL Server. I have a maintenance dataset and I built a new column converting strings about components failing into numbers ("comp1" becomes 1, "comp2" becomes 2 and so on). Then I built a two-way table with the number of the failed component as values and I wanted to use conditional formatting to highlight it, using red for 1, blue for 2 and so on.

However, I get all the numbers in red. I think it's because it counts the number of values (which is always 1, because I have just a value for every space of my table, which can be 1 or 2 or 3 or...), but I want it to look at the number instead. I am not able to change it in the settings, showing it to see at the number and not at how many numbers you have.

I could also use conditional formatting with the beginning column with "comp1", "comp2",... but it looks I can't use conditional formatting with non-numeric variables.

Please help me, it's very important to me.

Thank you!

[Thejeswar]

@Anonymous,

I am not exactly clear with what your requirement is, but from what I can see

1. You have a column the gives the failure status which you are using to create a numeric column to apply conditional formatting on

2. You have created the column, but it is not getting colored properly. But all the values are getting displayed as Red

3. I am not sure as to why you are having an additional two-way table to apply conditional formatting on your data

When I check based on the above points, I was able to perform conditional formatting using the numeric column. 

One place you can check that might be causing this issue is 
While you are specifying conditional formatting, if you are formatting based on rules, There is an option called summarization, which might be having agg. type as Count. Changing it to sum might help solve this issue

In case if my understanding on your requirement is not what you expected, help me in understanding what you actually need

Note: I am having my calculated columns in the same data set as my actual data

[Anonymous]

Well @Thejeswar thank you for help.

You really understood my issue, but it doesn't help very much. Points 1 and 2 are ok, then I have a two-way table to see failures on which machine (1st dimension) and on which day (2nd dimension).

When I get the "Background color scales" as the image you post, in the "Color based on" section I have "Count of my_calculated_column" and then in the "Summarization" field I can choose "Count" or "Count (distinct)" only. I don't know why I can't use Sum.

I have the same issue using my calculated column in another table which is only one-dimension.

[Thejeswar]

@Anonymous,

Can you check the data type and data format of your calculated column. I think it is a text.

Only in such case, the Summarization option will show count and count(Distinct) and not in when the data type and format are whole number or decimal number

Regards,

Thejeswar

[Anonymous]

@Thejeswar

Of course I did it, but when you create a new column like this the data type is general (in my Italian version it's "Qualsiasi", I think in English it's "Any" or "Whatever", the general type), it's not a specific one even if I use numbers only. I can't force it to number type because I am using data with DirectQuery import and changing data type is not supported in it. 

[Thejeswar · Accepted solution]

@Anonymous,

I tried to recreate your scenario but was not able to.

I am able to conditionally format the data as per your requirement even when connected in direct query mode

My Table Data is as follows

Year	Month	value	Comp
2018	Apr	10	Comp2
2018	Apr	20	Comp2
2018	Apr	30	Comp2
2018	Mar	10	Comp1
2018	Mar	20	Comp1
2018	Mar	30	Comp1
2018	May	10	Comp3
2018	May	20	Comp3
2018	May	30	Comp3

In the above table, the column "Comp" is a calculated column and the code is as follows (Comp is created in Query Editor Window)

= Table.AddColumn(dbo_TestLoad, "Comp", each if [Month] = "Mar" then "Comp1" else if [Month] = "Apr" then "Comp2" else "Comp3")

Then I create another calculated Column "Column". This time I am creating this is Power BI Desktop where all the fields are visible

Column = IF(TestLoad[Comp] = "Comp1", 1, IF(TestLoad[Comp] = "Comp2", 2, 3)) 

The Above column has Data Type and Data Format as Whole Number

Below is my report output after conditional formattingReport Output

For your reference, my conditional formatting rules

Formatting by Rules

[Anonymous]

@Thejeswar

It works! I don't know why I didn't think this way. I created my new column in Query Editor, I think it should be the same of your way, but it didn't. 

Thank you very much!!