Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |