Forum Discussion

POWER_MI's avatar
POWER_MI
Post Patron
5 years ago
Solved

if Condition Color

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

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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.

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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's avatar
      POWER_MI
      Post Patron
      5 years ago

      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.