cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
POWER_MI
Post Patron
Post Patron

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

1 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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
POWER_MI
Post Patron
Post Patron

@MFelix Thanks

it's work fine

amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

MFelix
Super User III
Super User III

Hi @POWER_MI ,

 

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

MFelix_0-1605522717885.png

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]
        )
    )
)

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi all

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

if A 

A.PNG

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

 

if B 

B.PNG

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.