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