Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
GMkk
Frequent Visitor

convert sql joins to dax

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.

GMkk_0-1712153696887.png

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

GMkk_1-1712153796305.png

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 



15 REPLIES 15
GMkk
Frequent Visitor

@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
Frequent Visitor

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_dyprdinv_valuedinv_fk_dyei
260722 3271260722
     
     
260725 3271260725
     
     
     
     
260770 3272260770
260799 3272260799
260808    
260809    
260812    
260813 3271260813
260831    
260826 3271260826

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

GMkk
Frequent Visitor

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)
164987191164987260722
164988   
164989   
164990191164990260725
164992   
164993   
164994   
164995   
164996191164996260770
164997191164997260799
164998191164998260808
164999191164999260809
165000191165000260812
165001191165001260813
165003191165003260831
165002191165002260826
GMkk
Frequent Visitor

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
Frequent Visitor

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_dyprdinv_valuedinv_fk_dyei
191164962260592
3271260573
3271260590
191164963260601
3272260587
3271260589
3271260592
3272260591
191164964260615
191164965260616
191164966260618
191164967260624
191164968260625
3272260089
3272260615
191164969260637
3272260625
191164970260640
191164971260650
191164972260651
3271260640
191164973260654
191164974260658
191164975260659
3271260651
191164976260662
3272260616
3272260658
3272260654
3272260662
3272260561
3272260650
191164977260696
191164978260697
3272260563
191164979260701
191164980260702
191164981260703
191164982260704
191164983260705
191164984260713
191164985260714
3272260702
3271260705
191164986260721
191164987260722
191164988260723
191164989260724
191164990260725
191164991260726
191164992260727
3271260722
3271260725
3271260723
3271260727
3272260697
191164993260751
191164994260752
3272260752
191164995260757
3272260703
3272260751
191164996260770
3271260757
3271260696
3272260770
3272260601
3271260618
3272260637
3272260659
3272260713
191164997260799
3272260726
3271260701
3272260724
3272260799
191164998260808
191164999260809
191165000260812
191165001260813
3271260588
3272260704
3271260714
3271260813
191165002260826
3271260826
191165003260831
GMkk
Frequent Visitor

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

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

GMkk
Frequent Visitor

@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_SOURCEdinv_fk_dyprdinv_valuedinv_fk_dyeidinv_fk_dyprdinv_fk_dyeidinv_value 
1649871911649872607223272607221 
1649901911649902607253272607251 
1649961911649962607703272608131 
164997191164997260799    
164998191164998260808    
164999191164999260809    
165000191165000260812    
165001191165001260813    


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.

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

 

lbendlin_0-1712453360184.png

 

GMkk
Frequent Visitor

dinv_fk_dyprdinv_valuedinv_fk_dyei
191164962260592
3271260573
3271260590
191164963260601
3272260587
3271260589
3271260592
3272260591
191164964260615
191164965260616
191164966260618
191164967260624
191164968260625
3272260089
3272260615
191164969260637
3272260625
191164970260640
191164971260650
191164972260651
3271260640
191164973260654
191164974260658
191164975260659
3271260651
191164976260662
3272260616
3272260658
3272260654
3272260662
3272260561
3272260650
191164977260696
191164978260697
3272260563
191164979260701
191164980260702
191164981260703
191164982260704
191164983260705
191164984260713
191164985260714
3272260702
3271260705
191164986260721
191164987260722
191164988260723
191164989260724
191164990260725
191164991260726
191164992260727
3271260722
3271260725
3271260723
3271260727
3272260697
191164993260751
191164994260752
3272260752
191164995260757
3272260703
3272260751
191164996260770
3271260757
3271260696
3272260770
3272260601
3271260618
3272260637
3272260659
3272260713
191164997260799
3272260726
3271260701
3272260724
3272260799
191164998260808
191164999260809
191165000260812
191165001260813
3271260588
3272260704
3271260714
3271260813
191165002260826
3271260826
191165003260831
ID_FROM_SOURCE
164987
164988
164989
164990
164992
164993
164994
164995
164996
164997
164998
164999
165000
165002
165003
165001
GMkk
Frequent Visitor

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

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
Frequent Visitor

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

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

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors