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

     

     

     

3 Replies

  • dbollini's avatar
    dbollini
    Icon for Helper II rankHelper II
    1 year ago
    users not Checkedin = CALCULATE(DISTINCTCOUNT(IPME_TeamMembers[EmpId]))-CALCULATE(DISTINCTCOUNT(ReportRideOutCheckInsView[Id]))
    But this is not giving me correct counts
  • 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

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    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 
)