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

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 :)

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi hxkresl

     

    Just a couple of questions that will help me.  So I understand the code is ok for the small sample dataset, yes?  :)

     

    Any chance you can give me a slightly bigger dataset including some duplication that you want to ignore.  It would be great if you can do this and also provide the expected result for the bigger dataset to help me pick the best place to apply the de-dup.

     

    Cheers,

     

    Phil

    • BalaVenuGopal's avatar
      BalaVenuGopal
      Resolver I
      8 years ago

      Hi Phil_Seamark ,

       

      I had heavy code writen in the stored procs to get the data which involves lot of temp table joins and where conditions .(Currently this proc is inserting data into sql table from there SSRS repor is rendering)

       

      If i want to convert this SSRS  to powerBi report which one would be faster DAX or get data from stored proc as source

      Which once would be technically best suits .

       

      Could you plese suggest.