topic Re: convert sql joins to dax in DAX Commands and Tips

convert sql joins to dax

GMkk — Wed, 03 Apr 2024 14:17:55 GMT

hi everyone, this is my sql
SELECT
COUNT(inc_incident_DW.inc_incident_ref) AS Inc_No
--,luynt_description
--,FeedbackYN.dinv_value
FROM inc_incident_DW
LEFT OUTER JOIN sta_station_DW ON inc_incident_DW.SK_sta_pk = sta_station_DW.SK_sta_pk
LEFT OUTER JOIN dinv_dynamic_int_value_DW AS customIncident ON customIncident.dinv_fk_dypr = 191 and customIncident.dinv_value = inc_incident_DW.ID_FROM_SOURCE
LEFT OUTER JOIN dinv_dynamic_int_value_DW AS FeedbackYN on FeedbackYN.dinv_fk_dypr = 327 and FeedbackYN.dinv_fk_dyei = customIncident.dinv_fk_dyei
LEFT OUTER JOIN luynt_yes_no_type_DW AS luynt ON luynt.ID_FROM_SOURCE = FeedbackYN.dinv_value

trying to replicate it into dax but this sql query has got self joins and i coudn't understand/get how to replicate in dax.

this is how it looks in sql design query editor, please feel free to ask any other info required.

this is in my analysis service , let me know if i need to edit relationships to achive what i need.

I appreciate everyone, for taking time and reading

Re: convert sql joins to dax

lbendlin — Wed, 03 Apr 2024 23:30:32 GMT

You already seem to be following the general guidelines to identify dimension and fact tables and to wire them accordingly. If the above model is appropriate for your business question then go ahead and use it.

Re: convert sql joins to dax

GMkk — Thu, 04 Apr 2024 07:20:12 GMT

lbendlin Thanks for your reply, could you please guide me the dax query for the above sql

Re: convert sql joins to dax

lbendlin — Thu, 04 Apr 2024 10:35:58 GMT

You don't need a query. Join your tables and apply the filters to your visual.

Re: convert sql joins to dax

GMkk — Thu, 04 Apr 2024 13:29:59 GMT

lbendlin i tried but didn't give me the same fig as sql

Re: convert sql joins to dax

lbendlin — Thu, 04 Apr 2024 14:10:32 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: convert sql joins to dax

GMkk — Fri, 05 Apr 2024 09:28:23 GMT

dinv_fk_dypr	dinv_value	dinv_fk_dyei
191	164962	260592
327	1	260573
327	1	260590
191	164963	260601
327	2	260587
327	1	260589
327	1	260592
327	2	260591
191	164964	260615
191	164965	260616
191	164966	260618
191	164967	260624
191	164968	260625
327	2	260089
327	2	260615
191	164969	260637
327	2	260625
191	164970	260640
191	164971	260650
191	164972	260651
327	1	260640
191	164973	260654
191	164974	260658
191	164975	260659
327	1	260651
191	164976	260662
327	2	260616
327	2	260658
327	2	260654
327	2	260662
327	2	260561
327	2	260650
191	164977	260696
191	164978	260697
327	2	260563
191	164979	260701
191	164980	260702
191	164981	260703
191	164982	260704
191	164983	260705
191	164984	260713
191	164985	260714
327	2	260702
327	1	260705
191	164986	260721
191	164987	260722
191	164988	260723
191	164989	260724
191	164990	260725
191	164991	260726
191	164992	260727
327	1	260722
327	1	260725
327	1	260723
327	1	260727
327	2	260697
191	164993	260751
191	164994	260752
327	2	260752
191	164995	260757
327	2	260703
327	2	260751
191	164996	260770
327	1	260757
327	1	260696
327	2	260770
327	2	260601
327	1	260618
327	2	260637
327	2	260659
327	2	260713
191	164997	260799
327	2	260726
327	1	260701
327	2	260724
327	2	260799
191	164998	260808
191	164999	260809
191	165000	260812
191	165001	260813
327	1	260588
327	2	260704
327	1	260714
327	1	260813
191	165002	260826
327	1	260826
191	165003	260831

ID_FROM_SOURCE

164987

164988

164989

164990

164992

164993

164994

164995

164996

164997

164998

164999

165000

165002

165003

165001

Re: convert sql joins to dax

GMkk — Fri, 05 Apr 2024 09:39:12 GMT

lbendlin , This is how data looks like
when dinv_fk = 191 , it should match its dinv_value with id_from_source , and dinv_fk_dyei value should match with irself for dinv_fk =327 , when dinv_fk is 327 , its dinv_value should be =1

ID_FROM_SOURCE	dinv_fk_dypr	dinv_value	dinv_fk_dyei	dinv_fk_dypr	dinv_fk_dyei	dinv_value
164987	191	164987	260722	327	260722	1
164990	191	164990	260725	327	260725	1
164996	191	164996	260770	327	260813	1
164997	191	164997	260799
164998	191	164998	260808
164999	191	164999	260809
165000	191	165000	260812
165001	191	165001	260813

so overall it should give me a measure as = 3, after passing all the conditions only 3 of them has cleared all filtering conditions.
once again a big thankyou for taking your time.

Re: convert sql joins to dax

lbendlin — Sun, 07 Apr 2024 01:29:27 GMT

According to your sample data the value is 2 for 260770 ?

Re: convert sql joins to dax

GMkk — Mon, 08 Apr 2024 07:17:52 GMT

Yes, you are right , 260722,260725,260813 are the only 3 that satisfies the condition from the above table

Re: convert sql joins to dax

lbendlin — Mon, 08 Apr 2024 15:07:12 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: convert sql joins to dax

GMkk — Tue, 09 Apr 2024 08:09:19 GMT

ID_FROM_SOURCE ( Table-A)
164987
164988
164989
164990
164992
164993
164994
164995
164996
164997
164998
164999
165000
165002
165003
165001

Table A- Table-B ( one to Many)
Table-B

dinv_fk_dypr	dinv_value	dinv_fk_dyei
191	164962	260592
327	1	260573
327	1	260590
191	164963	260601
327	2	260587
327	1	260589
327	1	260592
327	2	260591
191	164964	260615
191	164965	260616
191	164966	260618
191	164967	260624
191	164968	260625
327	2	260089
327	2	260615
191	164969	260637
327	2	260625
191	164970	260640
191	164971	260650
191	164972	260651
327	1	260640
191	164973	260654
191	164974	260658
191	164975	260659
327	1	260651
191	164976	260662
327	2	260616
327	2	260658
327	2	260654
327	2	260662
327	2	260561
327	2	260650
191	164977	260696
191	164978	260697
327	2	260563
191	164979	260701
191	164980	260702
191	164981	260703
191	164982	260704
191	164983	260705
191	164984	260713
191	164985	260714
327	2	260702
327	1	260705
191	164986	260721
191	164987	260722
191	164988	260723
191	164989	260724
191	164990	260725
191	164991	260726
191	164992	260727
327	1	260722
327	1	260725
327	1	260723
327	1	260727
327	2	260697
191	164993	260751
191	164994	260752
327	2	260752
191	164995	260757
327	2	260703
327	2	260751
191	164996	260770
327	1	260757
327	1	260696
327	2	260770
327	2	260601
327	1	260618
327	2	260637
327	2	260659
327	2	260713
191	164997	260799
327	2	260726
327	1	260701
327	2	260724
327	2	260799
191	164998	260808
191	164999	260809
191	165000	260812
191	165001	260813
327	1	260588
327	2	260704
327	1	260714
327	1	260813
191	165002	260826
327	1	260826
191	165003	260831

Re: convert sql joins to dax

GMkk — Tue, 09 Apr 2024 08:14:22 GMT

SELECT
COUNT(Id_From_source)
FROM A
LEFT OUTER JOIN B AS customIncident ON customIncident.dinv_fk_dypr = 191 and customIncident.dinv_value = A.ID_FROM_SOURCE
LEFT OUTER JOIN B AS FeedbackYN on FeedbackYN.dinv_fk_dypr = 327 and FeedbackYN.dinv_fk_dyei = customIncident.dinv_fk_dyei
where FeedbackYN.dinv_value=1

Re: convert sql joins to dax

GMkk — Tue, 09 Apr 2024 08:24:44 GMT

so our firstcondition (LEFT OUTER JOIN B AS customIncident ON customIncident.dinv_fk_dypr = 191 and customIncident.dinv_value = A.ID_FROM_SOURCE) looks like

ID_FROM_SOURCE(Table-A)	dinv_fk_dypr(Table-B)	dinv_value (Table-B)	dinv_fk_dyei (Table-B)
164987	191	164987	260722
164988
164989
164990	191	164990	260725
164992
164993
164994
164995
164996	191	164996	260770
164997	191	164997	260799
164998	191	164998	260808
164999	191	164999	260809
165000	191	165000	260812
165001	191	165001	260813
165003	191	165003	260831
165002	191	165002	260826

Re: convert sql joins to dax

GMkk — Tue, 09 Apr 2024 08:35:23 GMT

from second condition ( LEFT OUTER JOIN B AS FeedbackYN on FeedbackYN.dinv_fk_dypr = 327 and FeedbackYN.dinv_fk_dyei = customIncident.dinv_fk_dyei ) after self joining

dinv_fk_dyei	dinv_fk_dypr	dinv_value	dinv_fk_dyei
260722	327	1	260722


260725	327	1	260725




260770	327	2	260770
260799	327	2	260799
260808
260809
260812
260813	327	1	260813
260831
260826	327	1	260826

finally our mesure should count only where where FeedbackYN.dinv_value=1
so the overall measure should give me 4

Re: convert sql joins to dax

GMkk — Tue, 09 Apr 2024 08:36:39 GMT

lbendlin Thanks for taking your time, I hope the data which i have provided now make sense, please do let me know if you still need any more clarifications

Re: convert sql joins to dax

Anonymous — Tue, 30 Apr 2024 05:37:10 GMT

Hi GMkk

lbendlin , thanks for your concern about this case.

The following formula is for your reference:

Measure = VAR _tab1 = CALCULATETABLE(VALUES('Table-B'[dinv_fk_dyei]), FILTER(ALLSELECTED('Table-B'), 'Table-B'[dinv_fk_dypr] = 191 && 'Table-B'[dinv_value] in VALUES('Table-A'[ID_FROM_SOURCE]))) var _tab2 = CALCULATETABLE(VALUES('Table-B'[dinv_fk_dyei]), FILTER(ALLSELECTED('Table-B'), 'Table-B'[dinv_fk_dypr] = 327 && 'Table-B'[dinv_value] = 1 && 'Table-B'[dinv_fk_dyei] in _tab1)) return COUNTROWS(_tab2)

But I have a small doubt to confirm with you, I tested it in the database with the SQL statement you provided and the count is 7. If I have misunderstood you, could you please explain further? Thank you in advance for your time.

Result:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.