Forum Discussion
Conditional formatting works badly
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!
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
6 Replies
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 issueIn 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
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.
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