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

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
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.

    • hxkresl's avatar
      hxkresl
      Advocate III
      9 years ago

      I've applied DISTINCT to the DAX you gave since there are often duplicate reportname/fieldname pairs in the report table.  In the sample dataset this fixes issues of duplicate count, but in real dataset this DISTINCT isn't doing enough to handle for higher than "manual count" of numbers.  (SQL Query is handling duplicates well)

       

      You can see I've added DISTINCT

      #ReportAttributeCount = SELECTCOLUMNS(
                           SUMMARIZECOLUMNS(
                           'Report-AttributeMap'[Report Name],
                           "rptA - Total Attributes", COUNTROWS(DISTINCT('Report-AttributeMap'))
                           ),
                             "ReportA", 'Report-AttributeMap'[Report Name],
                             "rptA - Total Attributes", [rptA - Total Attributes]
)

       

       

      to get

      notice Compliance Details now has 4 totalreportA attributes instead of 6.

       

      No issues of inflated RACB.ReportA_AttributeCount, only with RAC.ReportA_AttributeCounts.

       

       Is there any other place in the DAX formulas that could be generating duplicates?

       

       

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Hi hxkresl,

         

        Just having a look at this now for you :)