Join Two Tables and Get the values

[dbollini]

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 on t.RideoutEventId = roe.RideOutEventID

       WHERE roe.RideOutEventID = @EventId

       AND (@SectionId = 0 OR t.SectionId = @SectionId);

I have to convert this SQL query to DAX where empid from tow tables match and join at Rideoutevent ID and Section id is 0

[sevenhills · Accepted solution]

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]

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

[dbollini]

users not Checkedin = CALCULATE(DISTINCTCOUNT(IPME_TeamMembers[EmpId]))-CALCULATE(DISTINCTCOUNT(ReportRideOutCheckInsView[Id]))

But this is not giving me correct counts