Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
Proud to be a Super User!
Check out my blog: Power BI em Português@POWER_MI , Selectedvalue should have done that. what is the challenge ?
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]
)
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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.
if B
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
Proud to be a Super User!
Check out my blog: Power BI em Portuguêshi 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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |