Forum Discussion
Solved
DAX equivalent to complex SQL query
I have SQL query that takes a two column table (ReportName, Attribute) and arranges data to show how much any two reports have in common, in terms of shared attributes, as count and percentage.
Here's what power bi report looks like using SQL dataset.
I want to create exactly same using DAX against source table (eliminate SQL query dependency). SQL query and inputs below.
CREATE TABLE #ReportTable (
ReportName varchar (20),
AttributeName varchar(30),
);
INSERT INTO #ReportTable (ReportName, AttributeName)
VALUES ('Compliance Details', 'Vendor Name'),
('Compliance Details', 'Area'),
('Compliance Details', 'Area'),
('Compliance Details', 'Area'),
('Compliance Details', 'Region'),
('Compliance Details', 'SubRegion'),
('Delivery and Invoice', 'Customer Name'),
('Delivery and Invoice', 'Area'),
('Delivery and Invoice', 'Region'),
('Delivery and Invoice', 'SubRegion'),
('Operations Review', 'Customer Name'),
('Operations Review', 'Approver'),
('Operations Review', 'Approval Status');
WITH ReportAttributeCount AS (
SELECT ReportName as ReportA, COUNT(AttributeName) AS ReportA_AttributeCount
FROM #ReportTable
GROUP BY ReportName
),
CommonAttributesCount AS (
SELECT RT.ReportName AS ReportA, RT2.ReportName AS ReportB, COUNT(DISTINCT RT.AttributeName) AS CommonAttributeCount --convert to DAX 1
FROM #ReportTable AS RT
INNER JOIN #ReportTable AS RT2
ON RT.ReportName < RT2.ReportName
AND RT.AttributeName = RT2.AttributeName
GROUP BY RT.ReportName, RT2.ReportName
)
SELECT DISTINCT
RAC.ReportA,
CACA.CommonAttributeCount,
CACA.ReportB, --convert to DAX 2
(CACA.CommonAttributeCount * 1.00) / RAC.ReportA_AttributeCount AS reportApercentage,--convert to DAX 3
RAC.ReportA_AttributeCount,
(CACA.CommonAttributeCount * 1.00) / RACB.ReportA_AttributeCount AS reportBpercentage,--convert to DAX 4
RACB.ReportA_AttributeCount AS ReportB_AttributeCount --convert to DAX 5
FROM ReportAttributeCount AS RAC
INNER JOIN CommonAttributesCount AS CACA
ON RAC.ReportA = CACA.ReportA
INNER JOIN ReportAttributeCount AS RACB
ON CACA.ReportB = RACB.ReportA;
DROP TABLE #ReportTable;
I want to begin by converting the CommonAttributeCount to DAX, and this requires a self join. I don't know where to begin. Pls help walk me through this.
Hi there,
Please try adding the following three calcuated tables:
ReportAttributeCount = SELECTCOLUMNS( SUMMARIZECOLUMNS( 'Table2'[ReportName], "ReportA_AttributeCount" , COUNTROWS('Table2') ), "ReportA",[ReportName], "ReportA_AttributeCount",[ReportA_AttributeCount] )CommonAttributesCount = SUMMARIZE( FILTER( GENERATE( SELECTCOLUMNS( Table2, "ReportName",'Table2'[ReportName], "AttributeName",'Table2'[AttributeName] ) , SELECTCOLUMNS( Table2, "ReportName2",'Table2'[ReportName], "AttributeName2",'Table2'[AttributeName] ) ),[AttributeName]=[AttributeName2] && [ReportName]< [ReportName2] ), [ReportName], [ReportName2], [AttributeName] )and finally
ReportTable = VAR CommonAttributesCount2 = SELECTCOLUMNS( SUMMARIZE( 'CommonAttributesCount', [ReportName], [ReportName2], "CommonAttributeCount",DISTINCTCOUNT('CommonAttributesCount'[AttributeName])),"CACA.ReportA",[ReportName],"CACA.ReportB",[ReportName2],"CACA.DC",[CommonAttributeCount]) VAR FINAL = ADDCOLUMNS( FILTER( CROSSJOIN( FILTER( CROSSJOIN( SELECTCOLUMNS( ReportAttributeCount, "RAC.ReportA",[ReportA], "RAC.ReportA_AttributeCount", [ReportA_AttributeCount] ), CommonAttributesCount2), [RAC.ReportA]=[CACA.ReportA] ), SELECTCOLUMNS( 'ReportAttributeCount', "RACB.ReportA",[ReportA], "RACB.ReportA_AttributeCount",[ReportA_AttributeCount] ) ),[CACA.ReportB]=[RACB.ReportA]) , "reportApercentage",DIVIDE([CACA.DC],[RAC.ReportA_AttributeCount]), "reportBpercentage",DIVIDE([CACA.DC],[RACB.ReportA_AttributeCount])) RETURN FINALwhich for me returns this as a result
7 Replies
Hi there,
Please try adding the following three calcuated tables:
ReportAttributeCount = SELECTCOLUMNS( SUMMARIZECOLUMNS( 'Table2'[ReportName], "ReportA_AttributeCount" , COUNTROWS('Table2') ), "ReportA",[ReportName], "ReportA_AttributeCount",[ReportA_AttributeCount] )CommonAttributesCount = SUMMARIZE( FILTER( GENERATE( SELECTCOLUMNS( Table2, "ReportName",'Table2'[ReportName], "AttributeName",'Table2'[AttributeName] ) , SELECTCOLUMNS( Table2, "ReportName2",'Table2'[ReportName], "AttributeName2",'Table2'[AttributeName] ) ),[AttributeName]=[AttributeName2] && [ReportName]< [ReportName2] ), [ReportName], [ReportName2], [AttributeName] )and finally
ReportTable = VAR CommonAttributesCount2 = SELECTCOLUMNS( SUMMARIZE( 'CommonAttributesCount', [ReportName], [ReportName2], "CommonAttributeCount",DISTINCTCOUNT('CommonAttributesCount'[AttributeName])),"CACA.ReportA",[ReportName],"CACA.ReportB",[ReportName2],"CACA.DC",[CommonAttributeCount]) VAR FINAL = ADDCOLUMNS( FILTER( CROSSJOIN( FILTER( CROSSJOIN( SELECTCOLUMNS( ReportAttributeCount, "RAC.ReportA",[ReportA], "RAC.ReportA_AttributeCount", [ReportA_AttributeCount] ), CommonAttributesCount2), [RAC.ReportA]=[CACA.ReportA] ), SELECTCOLUMNS( 'ReportAttributeCount', "RACB.ReportA",[ReportA], "RACB.ReportA_AttributeCount",[ReportA_AttributeCount] ) ),[CACA.ReportB]=[RACB.ReportA]) , "reportApercentage",DIVIDE([CACA.DC],[RAC.ReportA_AttributeCount]), "reportBpercentage",DIVIDE([CACA.DC],[RACB.ReportA_AttributeCount])) RETURN FINALwhich for me returns this as a result
Thank you, that's great that it can be done.
Working well with demo data:
Woohoo, nice work
If you break apart the code it kinda reads like SQL. Although in DAX, to do something similar to an SQL inner join, you can combine the FILTER(CROSSJOIN(....)...) functions. the rest is just renaming columns so you don't end up with two column names the same.
