Forum Discussion

dbollini's avatar
dbollini
Icon for Helper II rankHelper II
1 year ago
Solved

Join Two Tables and Get the values

Hello All,          SELECT @TotalTeamMemberCount = Count(DISTINCT tm.EmpId)        FROM IPME_TeamMembers tm        INNER JOIN IPME_Teams t on tm.TeamID = t.ID        INNER JOIN RideOutEvents roe...
  • sevenhills's avatar
    sevenhills
    1 year ago

    Are these three tables have relationships? i.e., from Teams to team members to ride out events, do they have relationships in place?

     

    Then try this. (Try first without ALL and try with ALL - one should work. )

     

     

     

    TotalTeamMemberCount = 
Calculate( 
 DISTINCTCOUNT( IPME_TeamMembers[EmpId]), 
 FILTER ( ALL('RideOutEvents'), RideOutEvents [RideOutEventID ] = @EventId ),
                     -- Replace with your variable or value
 FILTER ( ALL('IPME_Teams'), IPME_Teams [SectionId] IN { 0, @SectionId)) 
                     -- Replace with your variable or value 
)

     

     

     

shafiz_p's avatar
shafiz_p
Icon for Super User rankSuper User
1 year ago

Hi dbollini  It is better to provide representative data and desired output to get the accurate result. Anyway, you could try the below code to convert SQL to DAX:

TotalTeamMemberCount = 
CALCULATE(
    DISTINCTCOUNT(IPME_TeamMembers[EmpId]),
    FILTER(
        IPME_TeamMembers,
        IPME_TeamMembers[TeamID] IN 
        SELECTCOLUMNS(
            FILTER(
                IPME_Teams,
                IPME_Teams[RideoutEventId] = @EventId &&
                IPME_Teams[SectionId] = @SectionId -- Replace this with the desired ID
            ),
            "TeamID", IPME_Teams[ID]
        )
    )
)

 

Change EventID and SectionID accordingly.

 

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

 

Best Regards,
Shahariar Hafiz