Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
)
@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"
)
@Anonymous did you give this a try yet?
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?
@Anonymous Is it kindly possible for you to provide a SQL DDL please for these 3 tables?
Hi @Anonymous ,
Can you only use DAX or is PowerQuery also an option?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |