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 ( SELECTEDVA...
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.

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.

 

POWER_MI's avatar
POWER_MI
Post Patron
5 years ago

if B 

  • MFelix's avatar
    MFelix
    Super User
    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.

    • POWER_MI's avatar
      POWER_MI
      Post Patron
      5 years ago

      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