Measure using linked table

[sneem]

Hi !

I have a problem with my dax measure which doesn't seem to be complicated ...
I have two tables : Match (with columns team_1_id and team_2_id) and Details (of the match) linked by match_id, and a slicer to select the team.

I would like to get the number of victories if the team is playing at home.

No problem to get the selected team with SELECTEDVALUE.
I have to use CALCULATE function with FILTER but I don't know how to check if the selected team is playing home.

I tried this but that doesn't work correcly :

Win =

VAR Match_Team_Id_Home = ALLSELECTED('public match'[team_1_id])

RETURN

CALCULATE (

    COUNTROWS ( 'details' ),

    FILTER ( 'details', 'details'[team_1_final_score] > 'details'[team_2_final_score] && [SelectedTeamId] in Match_Team_Id_Home)

)

Thanks in advance !

[sneem · Accepted solution]

I finally found the solution which was simple.

I made the two relations inactive, and then I was able to access to the team_1_id and team_2_id like this:

Win_Home =

CALCULATE (

    COUNTROWS ( details ),

    FILTER (

        details,

        details[team_1_final_score] > details[team_2_final_score]

            &&  RELATED ( match[team_1_id] ) == [SelectedTeamId]

    )

)

and 

Win_Away =

CALCULATE (

    COUNTROWS ( details ),

    FILTER (

        details,

        details[team_2_final_score] > details[team_1_final_score]

            &&  RELATED ( match[team_2_id] ) == [SelectedTeamId]

    )

)

[amitchandak]

@sneem , try like

Win =
VAR Match_Team_Id_Home = ALLSELECTED('public match'[team_1_id])
RETURN
CALCULATE (
COUNTROWS ( 'details' ),
FILTER ( 'details', 'details'[team_1_final_score] > 'details'[team_2_final_score] && 'public match'[team_1_id] in Match_Team_Id_Home)
)

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[sneem]

hi @amitchandak 

Thank you for your help.

After investigation the relation between match.team_1_id and team.id was inactive (another relation was active instead).

This works after activation of the relation:

Win_Home =

CALCULATE (

    COUNTROWS ( details ),

    FILTER (

        details,

        details[team_1_final_score] > details[team_2_final_score]

            && RELATED ( match[team_1_id] ) == [SelectedTeamId]

    )

)

But, of course, this doesn't work if the team is playing away because the relation between match.team_2_id and team.id is still inactive:

Win_Away =

CALCULATE (

    COUNTROWS ( details ),

    FILTER (

        details,

        details[team_2_final_score] > details[team_1_final_score]

            && RELATED ( match[team_2_id] ) == [SelectedTeamId]

    )

)

I tried do use USERELATIONSHIP to activate the relation for this mesure but that doesn't work. Have you got an idea ?

This is the model (simplified):