How to write equivalent of SQL Joins in DAX

[Anonymous]

Hi All,

I have a sql query which I will have to convert to DAX as it is decided that we should use Power BI Dataset and DAX instead of SQL Query and convert all those reports into Power BI Dataset and DAX.

We already have set of sql queries which I want to convert into DAX but facing difficulties while converting sql joins like Inner Join, Left Outer into DAX

For ex. I have a query 

SELECT
ts_treaty_sp.r_object_id AS eif_r_object_id, REPLACE(CONVERT(varchar, ts_eif_r.eif_date, 106), ' ', '-') AS EIFDate
FROM
ts_treaty_sp
INNER JOIN ts_treaty_rp ON (ts_treaty_sp.r_object_id=ts_treaty_rp.r_object_id)
INNER JOIN ts_eif_r ON ts_treaty_rp.eif_info_id = ts_eif_r.r_object_id
WHERE
ts_treaty_sp.is_web_publishable = 1
--and ts_treaty_sp.r_object_id = '0800000280063d5a'
AND ts_eif_r.eif_date IS NOT NULL
ORDER BY
eif_r_object_id

which I want to convert into DAX but I am getting report builder errors like "All column arguments of the ALL/ALLNOBLANKROW/ALLSELECTED/REMOVEFILTERS function must be from the same table".

Kindly help me to how to proceed on this further

[smpa01 · Accepted solution]

@Anonymous  I am guessing you want this to be done through a measure and not through DAX query

EIFDate=
FORMAT (
    CALCULATE (
        MAX ( ts_eif_r[Date] ),
        VAR _1 =                --equivalent to INNER JOIN ts_treaty_rp 
            INTERSECT (        ---- ON ( ts_treaty_sp.r_object_id = ts_treaty_rp.r_object_id )  
                SUMMARIZE (    --- WHERE  ts_treaty_sp.is_web_publishable = 1
                    FILTER ( ts_treaty_sp, ts_treaty_sp[is_web_publishable] = 1 ),
                    ts_treaty_sp[r_object_id]
                ),
                VALUES ( ts_treaty_rp[r_object_id] )
            )
        VAR _2 =                 -- equivalent to INNER JOIN
            INTERSECT (          -- ON ts_treaty_rp.eif_info_id = ts_eif_r.r_object_id
                SUMMARIZE (      ---- where ts_eif_r.eif_date IS NOT NULL
                    FILTER ( ts_eif_r, ts_eif_r[Date] <> BLANK () ),
                    ts_eif_r[r_object_id]
                ),
                VALUES ( ts_treaty_rp[eif_info_id] )
            )
        VAR _3 =
            INTERSECT ( _2, _1 )
        RETURN
            CALCULATETABLE ( ts_eif_r, _3 )
    ),
    "dd-mmmm-yyyy" --Replace(CONVERT(VARCHAR, ts_eif_r.eif_date, 106), ' ', '-') AS EIFDate
)

Once you have it, bring the axis (ts_treaty_sp.r_object_id AS eif_r_object_id ) from ts_treaty_sp and drop the above measure.

Also, I don't know what is the relationship between all these tables. Spare more details if you can. But the above measure is relationship-proof, works with or without relationship.

One more

EIFDate=
FORMAT (
    CALCULATE (
        MAX ( ts_eif_r[Date] ),
        VAR _join1 =
            TREATAS (
                SUMMARIZE (
                    FILTER ( ts_treaty_sp, ts_treaty_sp[is_web_publishable] = 1 ),
                    ts_treaty_sp[r_object_id]
                ),
                ts_treaty_rp[r_object_id]
            )
        VAR _join2 =
            TREATAS (
                SUMMARIZE (
                    FILTER ( ts_eif_r, ts_eif_r[Date] <> BLANK () ),
                    ts_eif_r[r_object_id]
                ),
                ts_treaty_rp[eif_info_id]
            )
        RETURN
            FILTER (
                VALUES ( ts_eif_r[r_object_id] ),
                ts_eif_r[r_object_id] IN INTERSECT ( _join1, _join2 )
            )
    ),
    "dd-mmmm-yyyy"
)

[smpa01]

@Anonymous  did you give this a try yet?

[AlexisOlson]

What are you ultimately trying to do here? Are you trying to define a new calculated table to add to your data model or trying to define a table you'll be using inside a measure or what?

What are the relevant tables and relationships in the dataset model?

[smpa01]

@Anonymous  Is it kindly possible for you to provide a SQL DDL please for these 3 tables?

[jppv20]

Hi @Anonymous ,

Can you only use DAX or is PowerQuery also an option?

[Anonymous]

Hi @jppv20 

DAX is the only option