Forum Discussion
Helper IIJoin 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 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
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 )
3 Replies
dbolliniusers not Checkedin = CALCULATE(DISTINCTCOUNT(IPME_TeamMembers[EmpId]))-CALCULATE(DISTINCTCOUNT(ReportRideOutCheckInsView[Id]))But this is not giving me correct counts- shafiz_p
Super User
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 - sevenhills
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 )
