Color column conditions

[POWER_MI]

Hi All,

in the source table 

it's like this 

In the Power BI three mesure are calculated based on this table :

Mesure_1_NBR_R1=

CALCULATE(

    COUNTA('table'[column]),

    'table'[column] IN {"R1"}

Mesure_2_NBR_R2=

CALCULATE(

    COUNTA('table'[column]),

    'table'[column] IN {"R2"}

Mesure_3_NBR_R3=

CALCULATE(

    COUNTA('table'[column]),

    'table'[column] IN {"R3"}

After that on Power BI dashBoard it's needed to have results like this 

Because when i do like explained in answer all rows is Red because i take like reference color column 

ColorFormat =
SWITCH (
TRUE (),
MAX ( 'Table'[Column] ) = "R1", "Red",
MAX ( 'Table'[Column] ) = "R2", "Green",
MAX ( 'Table'[Column] ) = "R3", "Pink "
)

we will color  'Table'[Column]  but we need to color the values of number of R1,R2,R3 witch calculated with mesure mesure not column 

Mesure_1_NBR_R1=

CALCULATE(

    COUNTA('table'[column]),

    'table'[column] IN {"R1"}

Mesure_2_NBR_R2=

CALCULATE(

    COUNTA('table'[column]),

    'table'[column] IN {"R2"}

Mesure_3_NBR_R3=

CALCULATE(

    COUNTA('table'[column]),

    'table'[column] IN {"R3"}

when doing 

ColorFormat =
SWITCH (
TRUE (),
MAX ( 'Table'[Mesure_1_NBR_R1] ) = "R1", "Red",
MAX ( 'Table'[Mesure_2_NBR_R2] ) = "R2", "Green",
MAX ( 'Table'[Mesure_3_NBR_R3] ) = "R3", "Pink "
)

the error is 

Column "Mesure_1_NBR_R1" in table "Table" could not be found and is not used in this expression.

Column "Mesure_2_NBR_R2" in table "Table" could not be found and is not used in this expression.

Column "Mesure_3_NBR_R3" in table "Table" could not be found and is not used in this expression.

I means to say that we need to color Number of  and not colum values.

Number of R1, Number of R2 and R3.

i checked one thing : when mesure is calculation one icone like this    

the color format is not working 

but when it's like this  it's work fine

For our demande it's number so calculate mesure

thanks for help

[v-deddai1-msft · Accepted solution]

Hi @POWER_MI ，

You can create a table like :

Then use the following measure :

Measure 2 = CALCULATE(COUNTA(COLOR_FIELD_TO_ADD[Coulumn]),FILTER(COLOR_FIELD_TO_ADD,NOT(ISERROR(FIND(COLOR_FIELD_TO_ADD[Coulumn],MAX('Table'[ROWS]))))))

Set background color for [measure2] by field [colorformat]:

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYCIfcnfrjNKoZ4x1H...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[POWER_MI]

Hi all, 

@v-deddai1-msft  thanks a lot for your answers.

In this solution the unique table to add is table choice to select Color condition A, B or C.

The update will be done on same table COLOR_FIELD_TO_ADD  and it's just asked to update 

ColorFormat to make the Row Grouped like described on Picture 

IF A 

MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R1", "R2", "R31" }, "Red",  ONE ROW on DAshbord TABLE results (R1", "R2", "R31)
MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R2", "R32" }, "Green", ONE ROW on DAshbord TABLE results

R2", "R32
MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R21" }, "Pink, ONE ROW on DAshbord TABLE results

 R21

it can be some thing like Grouped By ?

[POWER_MI]

Other Solution Her

https://community.powerbi.com/t5/Translated-Spanish-Desktop/si-Condition-Color/m-p/1496725#M145796

[POWER_MI]

Hi All, 

have you some Updates to have Three rows on Table resultats by grouping choice A( 3 rows), B ( 3 rows) C ( 3 rows) and the differece is on 3 rows Criteria Ri...

thanks

@v-deddai1-msft @amitchandak 

[v-deddai1-msft]

Hi @POWER_MI ,

You can create a slicer table for slicer:

Then you can use the following colorformat:

ColorFormat =
IF (
    SELECTEDVALUE ( Slicer[Value] ) = "A",
    SWITCH (
        TRUE (),
        MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R1", "R2", "R31" }, "Red",
        MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R2", "R32" }, "Green",
        MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R21" }, "Pink "
    ),
    IF (
        SELECTEDVALUE ( Slicer[Value] ) = "B",
        SWITCH (
            TRUE (),
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R1" }, "Red",
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R2", "R22", "R31", "R21" }, "Green",
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R32" }, "Pink "
        ),
        SWITCH (
            TRUE (),
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R1" }, "Red",
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R22" }, "Green",
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R2", "R32", "R31", "R21" }, "Pink"
        )
    )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[POWER_MI]

Hi all,

@v-deddai1-msft 

thanks for your feed back. 

This is ok but on the table results we will have 3 rows which equale to If condition 

So we will have three rows on dash board table results depanding one Choice A,B C

IF A results will be like 

On dashbord results 

                                  

If B it'w ill be change like 

not like 

R1  ...

R2

R21

R22

...

thanks

[v-deddai1-msft]

Hi @POWER_MI ,

Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQToaVM-Vo9Cn58eS9...

If this post help, please consider accept it as the solution to help other member find it more quickly.

Best Regards,

Dedmon Dai

[POWER_MI]

hi @v-deddai1-msft 

thanks a lot for you answer. 

i have detailed some updates with some updates of problemes :The objectif is to add on Power Bi dashboard Button or list where the user can chose here color step :

The Value will be same count of Ri at Step i.

See picture 

[POWER_MI]

The Row of Power BI dashbord will be same 

Table 3 rows and 7 column for Step.

In Rows it's will depand on choice of user If A table 1 will be displayed, if B table 2 if C table 3 depending on R1... color like explained on picture.

Thanks a lot for help

You can find Power BI project her "COLOR FILED TO ADD"

Link : click here

[POWER_MI]

hello all, 

@v-deddai1-msft  thanks for your feed back. 

Here the Values is Number of R1, Number of R2 and Number of R3

We used three mesure to calculte this 

mesure 1=CALCULATE( COUNT('table'[column]), 'table'[column] IN "R1")

mesure 2=CALCULATE( COUNT('table'[column]), 'table'[column] IN "R2")

mesure 3=CALCULATE( COUNT('tablae[column]), 'table'[column] IN "R3")

Is it possible to make one Mesure to calculate number R1 and R2 and R3?

Also the Value on table will be number of R1,R2,R3 depeding on STEP i mean 

if step 1 && 'table'[column]="R1" color Green

if step 2 && 'table'[column]="R1" color Green

if step 3 && 'table'[column]="R1" color Green

if step 4 && 'table'[column]="R1" color ORANGE

if step 5 && 'table'[column]="R1" color ORANGE

if step 6 && 'table'[column]="R1" color RED

[POWER_MI]

this results need

you can find Power Bi project in this Link ( you click on Telecharger le fichier " donwlowd file
you can update file Power Bi and sent it
Link to download file : click here

[v-deddai1-msft]

Hi @POWER_MI ,

I can't understand your issue very well from your description . Based on my understanding, you can use matrix visual instead of table visual. And you can use one measure instead of three measures:

And you can the [color format]:

ColorFormat = 
SWITCH (
TRUE (),
MAX ( 'Table'[Column] ) = "R1", "Red",
MAX ( 'Table'[Column] ) = "R2", "Green",
MAX ( 'Table'[Column] ) = "R3", "Pink "
)

If this post help, please consider accept it as the solution to help other member find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@POWER_MI , When they are different measure each having color you can give static color to measure. Conditional formatting is needed when there is something dynamics .

Anyways as measures are returning number, you can not compare with the text

Try like

ColorFormat =
SWITCH (
TRUE (),
not(isblank(MAX ( 'Table'[Mesure_1_NBR_R1] ))), "Red",
not(isblank(MAX ( 'Table'[Mesure_2_NBR_R2] ))), "Green",
not(isblank(MAX ( 'Table'[Mesure_3_NBR_R3] ))), "Pink "
)

Also refer

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[POWER_MI]

hi @amitchandak 

Column "Measure_1_NBR_R2" in table "Table" could not be found and is not used in this expression.

Column "Measure_1_NBR_R1" in table "Table" could not be found and is not used in this expression.

Column "Measure_1_NBR_R3" in table "Table" could not be found and is not used in this expression.

[POWER_MI]

Hi @amitchandak 

i send error when doing with swhich is not blanck.

to be clear 

The begening condition is to color windows of values witch is the number of 'R1, nmber of R2 at differents step:

So the if condition will be 

If Step 1 and status R1  the color of values "Number of R1 will be RED. 

the number of R1 is calcultated with mesure

Mesure_1_NBR_R1

CALCULATE( COUNTA('tabla'[column]), 'table'[column] IN "R1"

and same alos for R2.

Also for example 

for step 1 and Step 6 the color of Number of values will be different so why it's mandatory to have conditions like swhich condition or if .. and not regulare or concrete values because the color of our windows number of values will be different on step 1 and step 6 or 7. 

For example Number of R1 is Red on step 1 but will be orange on Step 7 ( if step =7 number of R1 value windows will be orange)

hope that it s clear thanks for help