DAX Query to Group by and Join

[---Andrea---]

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.

[v-csrikanth · Accepted solution]

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.

[johnt75]

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