CODEWITHSUNDEEP
group-bydaxsummarize
Hell-1931
Hell-1931

Reputation: 499

Summarizing unique values grouped by different fields in DAX Power BI

I have the following table - Tbl1:

      ProgramID   Domain  ClientID  DateIn      DateOut     StatusDays  StatusDays_Upd
         471         Res     323       09/13/2019  09/16/2019   4          4
         471         Res     323       09/14/2019  09/16/2019   3          4
         471         Res     323       09/15/2019  09/16/2019   2          4
         471         Res     323       09/16/2019  09/16/2019   1          4

         471         Res     325       08/12/2019  08/13/2019   2          2
         471         Res     325       08/13/2019  08/13/2019   1          2

         471         Res     318       10/10/2019  10/13/2019   4          4
         471         Res     318       10/11/2019  10/13/2019   3          4
         471         Res     318       10/12/2019  10/13/2019   2          4
         471         Res     318       10/13/2019  10/13/2019   1          4

I need to create a Measure to summarize values of [StatusDays_Upd], grouped by [ProgramID], [Domain], [ClientID] and [DateOut] and also to count unique [ClientID]

So, that the result would be as the following:

       ProgramID  Count_ClientID Total_StatusDays_Upd
       471        3             10

[Total_StatusDays_Upd] should be 4+2+4=10 and must include all distinct values grouped by [DateOut]

I used the following measure for [Count_Clients]:

       Count_ClientID = Distinctcount(ClientID)

But can't figure the [Total_StatusDays_Upd] measure

Upvotes: 0

Views: 146

Answers (1)

Joao Leal
Joao Leal

Reputation: 5542

You can use a summarize in your measure to group by the required fields and then return the calculation on that:

[Total_StatusDays_Upd]=
SUMX(SUMMARIZE(Tbl1, 
               Tbl1[ProgramID], 
               Tbl1[DateOut], 
               Tbl1[ClientID], 
               Tbl1[StatusDays_Upd]), 
     Tbl1[StatusDays_Upd])

Upvotes: 2

Related Questions