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 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.