convert sql joins to dax

[GMkk]

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 

[v-xuxinyi-msft · Accepted solution]

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.

[GMkk]

@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 

[GMkk]

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

[GMkk]

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

[GMkk]

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

[GMkk]

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

[GMkk]

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

[lbendlin]

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

[GMkk]

@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.

[lbendlin]

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

[GMkk]

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

[GMkk]

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

[lbendlin]

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

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/1447...

[GMkk]

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

[lbendlin]

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

[lbendlin]

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.