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, 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 !

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

3 Replies

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

       

       
  • sneem's avatar
    sneem
    Frequent Visitor
    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]
        )
    )