cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to write equivalent of SQL Joins in DAX

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

 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

6 REPLIES 6
smpa01
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


@Anonymous  did you give this a try yet?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


AlexisOlson
Super User
Super User

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
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


jppv20
Solution Sage
Solution Sage

Hi @Anonymous ,

 

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

Anonymous
Not applicable

Hi @jppv20 

 

DAX is the only option

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors