if Condition Color

[POWER_MI]

Hi All, 

in this projet Power Bi we tried to calculate the Number of status Ri depending on A,B,C condition ( to fixe color).

The Mesures format is 

Color_Format_1 = 
IF (
    SELECTEDVALUE ( Tableau1[Choice] ) = "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 ( Tableau1[Choice] ) = "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"
        )
    )
)

is it possible to Make the results depending on choice A,B,C and on Three Lines on table and not 6 like on Power BI results 

Three Row Like A,B,C condition fixed

The Project is on enclosed link click here

thanks for help

[POWER_MI]

@MFelix Thanks

it's work fine

[amitchandak]

@POWER_MI , Selectedvalue should have done that. what is the challenge ?

[MFelix]

Hi @POWER_MI ,

Use the same formula you have for the couloring but for with the calculation of measure1:

Calculation = 
IF (
    SELECTEDVALUE ( Tableau1[Choice] ) = "A";
    SWITCH (
        TRUE ();
        MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R1"; "R2"; "R31" }; [Measure1];
        MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R2"; "R32" }; [Measure1];
        MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R21" }; [Measure1]
    );
    IF (
        SELECTEDVALUE ( Tableau1[Choice] ) = "B";
        SWITCH (
            TRUE ();
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R1" }; [Measure1];
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R2"; "R22"; "R31"; "R21" }; [Measure1];
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R32" }; [Measure1]
        );
        SWITCH (
            TRUE ();
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R1" };[Measure1];
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R22" }; [Measure1];
            MAX ( 'COLOR_FIELD_TO_ADD'[Coulumn] ) IN { "R2"; "R32"; "R31"; "R21" }; [Measure1]
        )
    )
)

[POWER_MI]

Hi all

@MFelix thanks a lot for your answer. The results is to have on Coulumn 1 values the Rows

if A 

and not A,B,C this one will be like filtre. and in the Values the count of Ri depending on Step.

[POWER_MI]

if B 

[MFelix · Accepted solution]

Hello @POWER_MI ,

Try to redo the table as follows

TypeLevelValue

A	A1	R1, R2, R31
A	A2	R2, R32
A	A3	R21
B	B1	R1
B	B2	R2, R22, R31, R21
B	B3	R32
C	B1	R1
C	B2	R22
C	B3	R2, R32, R31, R21

Now change your measurements to:

Calculation = 
    SWITCH (
        TRUE ();
        SELECTEDVALUE( 'Table'[Level] ) = "A1"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN  { "R1"; "R2"; "R31" });
         SELECTEDVALUE( 'Table'[Level] ) = "A2"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN  { "R22"; "R32" });
        SELECTEDVALUE( 'Table'[Level] ) = "A3"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN  { "R21" });
        SELECTEDVALUE( 'Table'[Level] ) = "B1"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN  { "R1"});
        SELECTEDVALUE( 'Table'[Level] ) = "B2"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN  { "R2"; "R22"; "R31"; "R21" });
        SELECTEDVALUE( 'Table'[Level] ) = "B3"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN  { "R32" });
        SELECTEDVALUE( 'Table'[Level] ) = "C1"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN  { "R1" });
        SELECTEDVALUE( 'Table'[Level] ) = "C2"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN  { "R22" });
        SELECTEDVALUE( 'Table'[Level] ) = "C3"; CALCULATE([Measure1]; COLOR_FIELD_TO_ADD[Coulumn] IN  { "R2"; "R32"; "R31"; "R21"  })
    )

Formatting = 
    SWITCH (
        TRUE ();
        SELECTEDVALUE( 'Table'[Level] ) = "A1"; "Red";
        SELECTEDVALUE( 'Table'[Level] ) = "A2"; "Green";
        SELECTEDVALUE( 'Table'[Level] ) = "A3"; "Pink";
        SELECTEDVALUE( 'Table'[Level] ) = "B1"; "Red";
        SELECTEDVALUE( 'Table'[Level] ) = "B2"; "Green";
        SELECTEDVALUE( 'Table'[Level] ) = "B3"; "Pink";
        SELECTEDVALUE( 'Table'[Level] ) = "C1"; "Red";
        SELECTEDVALUE( 'Table'[Level] ) = "C2"; "Green";
        SELECTEDVALUE( 'Table'[Level] ) = "C3"; "Pink"
    )

See the result connection.

[POWER_MI]

hi all

@MFelix  i have one question 

If for example on table dash board it's the results is for  Stage = Stage1, Stage 2, Stage 3 and Stage 4

And it's asked to make to table results 

Table 1 for STAGE 

and 

Table 2 Value of STAGE - 1

For example if we need Table Stage 4 results it will have 

Table 1 : STAGE 4

And Table 2 : STAGE 3 = STAGE -1 ( precedent one it's like Year and Year-1

- when STAGE is Number like 1,2,3 or caractere's like on project example STAGE 1, Stage 2 ... and how to do to stage STAGE 1- 1 = STAGE 0 not exist ? 

How to traduct it ?

you can find project her 

thanks for help