Calculate distinct based on the two columns

[Saxon10]

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

[Jos_Woolley · Accepted 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

[AlexisOlson · Accepted solution]

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"
    )

[Saxon10 · Accepted solution]

Thanks for your reply and solution for @Jos_Woolley  and @AlexisOlson and sorry for the late reply. 

Both solutions are working well. 

[AlexisOlson · Accepted solution]

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"
    )

[Jos_Woolley · Accepted 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