Forum Discussion
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.
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
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
16 Replies
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.
You don't need a query. Join your tables and apply the filters to your visual.
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
ID_FROM_SOURCEdinv_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 164987 164988 164989 164990 164992 164993 164994 164995 164996 164997 164998 164999 165000 165002 165003 165001 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 =1ID_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.According to your sample data the value is 2 for 260770 ?
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.
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-Bdinv_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 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=1so 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 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
