Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Group by in PowerBI Destop Direct Query

How can I do group-by and Having clause using Measure? I have Slicers for  "Group Number" and "Group Product Key".

 

SELECT CG."Group Number" || '-' || PT."Product NDC" "Group Product Key",
CG."Group Number",
PT."Product NDC",
PT."Pharmacy NABP",
PS."Pharmacy Name",
PS."Pharmacy City",
PS."Pharmacy State",
PT."Day Date",
PT."Day Time",
Sum(PT."# of Net Redemptions")

FROM REPORTS_MV."RXS-DailyFactsByClaimNumber" PT
JOIN REPORTS_MV."RXS-PharmacyStoreDimension" PS
ON PT."Pharmacy NABP" = PS."Pharmacy NABP"
JOIN REPORTS_MV."RXS-CardGroupDimension" CG
ON CG."Group Number" = PT."Group Number"
JOIN REPORTS_MV."RXS-CardGroupProductXref" CGPX
ON CGPX."Group Number" = CG."Group Number"
AND CGPX."Product NDC" = PT."Product NDC"
Group by CG."Group Number" || '-' || PT."Product NDC",
CG."Group Number",
PT."Product NDC",
PT."Pharmacy NABP",
PS."Pharmacy Name",
PS."Pharmacy City",
PS."Pharmacy State",
PT."Day Date",
PT."Day Time"
Having Sum(PT."# of Net Redemptions") >=2

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

     

    Above query returns a table as result, while measure returns a single value as result. For your requirement, you may need to create a calculated table with CROSSJOIN, SUMMARIZE and FILTER function. 

     

    Best regards,

    Yuliana Gu