Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 f...
Need Help
  • Thejeswar's avatar
    Thejeswar
    8 years ago

    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

     

    YearMonthvalueComp
    2018Apr10Comp2
    2018Apr20Comp2
    2018Apr30Comp2
    2018Mar10Comp1
    2018Mar20Comp1
    2018Mar30Comp1
    2018May10Comp3
    2018May20Comp3
    2018May30Comp3

     

     

    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

     

     

     

Thejeswar's avatar
Thejeswar
Super User
8 years ago

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

 

YearMonthvalueComp
2018Apr10Comp2
2018Apr20Comp2
2018Apr30Comp2
2018Mar10Comp1
2018Mar20Comp1
2018Mar30Comp1
2018May10Comp3
2018May20Comp3
2018May30Comp3

 

 

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's avatar
Anonymous
Not applicable
8 years ago

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!!