topic Re: DAX Query to Group by and Join in DAX Commands and Tips

DAX Query to Group by and Join

---Andrea--- — Wed, 26 Mar 2025 15:38:41 GMT

Good afternoon, I need to convert a short SQL query to PowerBI DAX.

As a reference, here is the SQL query:

WITH ae_per_infant AS ( SELECT Infants, COUNT(*) AS Count FROM ae GROUP BY Infants ) SELECT dm.UCRFID AS UCRFID, ae_per_infant.Count AS Count, COALESCE(ae_per_infant.Count / dm.duration, 0) AS IR FROM dm LEFT JOIN ae_per_infant ON dm.UCRFID = ae_per_infant.Infants

As you can see, the operations are a groupby-aggregate, a join and a simple column operation.

In PowerBI, there is an existing 1-to-many relation between DM and AE with bi-directional filtering active.

In my case, since I need this table to update based on filters, I can't perform the calculation in PowerQuery M in advance. To my knowledge, I can only use DAX.

Any help would be greatly appreciated.

Re: DAX Query to Group by and Join

johnt75 — Wed, 26 Mar 2025 16:25:52 GMT

Change the relationship so that it is single direction - bi-directional relationships are almost never a good idea.

You can create a couple of measures like

Infant count = COUNTROWS( AE ) IR = SUMX ( DM, DIVIDE ( COUNTROWS ( RELATEDTABLE ( AE ) ), DM[Duration], 0 ) )

Re: DAX Query to Group by and Join

v-csrikanth — Thu, 27 Mar 2025 09:02:14 GMT

Hi ---Andrea---
Thank you for being part of the Microsoft Fabric Community.

Please find below DAX code as per your requirement.

***********************************************
left join =

VAR A=
SELECTEDCOLUMNS(ae,"Infants",ae[Infants])
VAR B=
SELECTEDCOLUMNS(dm,"UCRFID",dm[UCRFID])
return
NATURALLEFTOUTERJOIN(B,A)

VAR IR =

IF(
ISBLANK(DIVIDE ( COUNTROWS ( RELATEDTABLE ( AE ) ), DM[Duration])),0

)
return IR

Infants count= COUNTROWS( AE )

***********************************************

Hope this helps!

If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.