none
DAX Subquery

    Question

  • select SUM(A.PageHits) from
    (SELECT count(DNS) as PageHits, dns
      FROM .[dbo].Test
      where Type = 'R' and areacode = 'AU'
      group by dns
     ) A

    Please help on converting above SQL subquery to DAX measure in PBI. 

    Thursday, July 12, 2018 11:17 PM

All replies

  • Hi Sa1991,

    Thanks for your question.

    >>>Please help on converting above SQL subquery to DAX measure in PBI.
    Please try below DAX formula:

    DesiredResult =
    VAR SubTest =
        SUMMARIZE (
            FILTER ( 'Test', 'Test'[Type] = "R" && 'Test'[areacode] = "AU" ),
            'Test'[DNS],
            "PageHits", COUNT ( 'Test'[DNS] )
        )
    RETURN
        SUMX ( [SubTest], [PageHits] )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Friday, July 13, 2018 11:18 AM
    Friday, July 13, 2018 1:25 AM
    Moderator
  • There doesn't seem to be a need for a subquery, since a sum of partial counts is just a total count.


    Expect me to help you solve your problems, not to solve your problems for you.

    Friday, July 13, 2018 12:07 PM