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.
In data table I have 3 columns are Team, Id and result.
In result column contain win, loss and no result.
If same team and id contain win, loss and no result then return "Multiple".
If same team and id contain win then return "win".
If same team and id contain loss then return "loss".
If same team and id contain win and no result then return "win".
If same team and id contain loss and no result then return "loss".
Team | ID | Result | Desired Report |
TR | 1S | Win | Multiple |
TR | 1S | Win | Multiple |
TR | 1S | No Result | Multiple |
TR | 1S | No Result | Multiple |
TR | 1S | Loss | Multiple |
TR | 1S | Loss | Multiple |
TR | 2S | Win | Multiple |
TR | 2S | Win | Multiple |
TR | 2S | Loss | Multiple |
TR | 2S | Loss | Multiple |
TR | 3S | Loss | Loss |
TR | 3S | Loss | Loss |
TR | 4S | Win | Win |
TR | 4S | Win | Win |
TR | 5S | No Result | Win |
TR | 5S | win | Win |
TR | 6S | Loss | Loss |
TR | 6S | No Result | Loss |
Solved! Go to Solution.
Hi,
NewColumn =
VAR MyTeam = 'Table'[Team]
VAR MyID = 'Table'[ID]
VAR MyTeamIDWins =
CALCULATE (
COUNTROWS ( FILTER ( 'Table', 'Table'[Team] = MyTeam && 'Table'[ID] = MyID ) ),
'Table'[Result] = "Win"
)
VAR MyTeamIDLosses =
CALCULATE (
COUNTROWS ( FILTER ( 'Table', 'Table'[Team] = MyTeam && 'Table'[ID] = MyID ) ),
'Table'[Result] = "Loss"
)
RETURN
SWITCH (
1 * ( MyTeamIDWins > 0 ) + 2 * ( MyTeamIDLosses > 0 ),
1, "Win",
2, "Loss",
3, "Multiple"
)
Regards
How about this?
Desired Report =
VAR Results =
CALCULATETABLE (
VALUES ( Table1[Result] ),
ALLEXCEPT ( Table1, Table1[Team], Table1[ID] ),
Table1[Result] <> "No Result"
)
RETURN
SWITCH (
TRUE (),
COUNTROWS ( Results ) = 0, "No Result",
COUNTROWS ( Results ) = 1, Results,
COUNTROWS ( Results ) > 1, "Multiple"
)
Thanks for your reply and solution for @Jos_Woolley and @AlexisOlson and sorry for the late reply.
Both solutions are working well.
Thanks for your reply and solution for @Jos_Woolley and @AlexisOlson and sorry for the late reply.
Both solutions are working well.
How about this?
Desired Report =
VAR Results =
CALCULATETABLE (
VALUES ( Table1[Result] ),
ALLEXCEPT ( Table1, Table1[Team], Table1[ID] ),
Table1[Result] <> "No Result"
)
RETURN
SWITCH (
TRUE (),
COUNTROWS ( Results ) = 0, "No Result",
COUNTROWS ( Results ) = 1, Results,
COUNTROWS ( Results ) > 1, "Multiple"
)
Hi,
NewColumn =
VAR MyTeam = 'Table'[Team]
VAR MyID = 'Table'[ID]
VAR MyTeamIDWins =
CALCULATE (
COUNTROWS ( FILTER ( 'Table', 'Table'[Team] = MyTeam && 'Table'[ID] = MyID ) ),
'Table'[Result] = "Win"
)
VAR MyTeamIDLosses =
CALCULATE (
COUNTROWS ( FILTER ( 'Table', 'Table'[Team] = MyTeam && 'Table'[ID] = MyID ) ),
'Table'[Result] = "Loss"
)
RETURN
SWITCH (
1 * ( MyTeamIDWins > 0 ) + 2 * ( MyTeamIDLosses > 0 ),
1, "Win",
2, "Loss",
3, "Multiple"
)
Regards
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |