Forum Discussion
Saxon10
Post Prodigy
Post ProdigyCalculate distinct based on the two columns
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 |
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.
3 Replies
- Jos_Woolley
Solution Sage
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
- AlexisOlson
Super User
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" ) 
Saxon10Thanks for your reply and solution for Jos_Woolley and @AlexisOlson and sorry for the late reply.
Both solutions are working well.