Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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

MFelix
Super User
Super User

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



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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.