Forum Discussion

sneem's avatar
sneem
Frequent Visitor
3 years ago
Solved

Measure using linked table

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,...
Need Help
  • sneem's avatar
    sneem
    3 years ago

    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's avatar
amitchandak
Super User
3 years ago

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's avatar
sneem
Frequent Visitor
3 years ago

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