Forum Discussion

hxkresl's avatar
hxkresl
Advocate III
9 years ago
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.    ...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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 FINAL

    which for me returns this as a result

     

hxkresl's avatar
hxkresl
Advocate III
9 years ago

Thank you, that's great that it can be done.  

 

Working well with demo data:

 

 

 

 

 

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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.