topic Re: Need Help Summing a Distinct Count in a Matrix Across all Rows in DAX Commands and Tips

Need Help Summing a Distinct Count in a Matrix Across all Rows

DTGPete — Thu, 15 Jun 2023 23:35:34 GMT

Hello,

I'm trying to sum a distinct count of days within a Matrix in Power BI. There are 3 distinct dates (8/1 through 8/3) with a value of 1 for 'Unique Days' across all rows - see image below.

When I drill up to the 'Customer Name' I expect the value to sum to 3, as there are 3 distinct dates associated across all rows - see Image below.

Formula's currently being used:

Unique Days =
If( DS_main_for_usa[BILLABLE_HOURS] > 0 || DS_main_for_usa[NON_BILLABLE_HOURS] > 0,
IF (CONTAINSSTRING(DS_main_for_USA[TASK_NAME], "- Canada -"),
CALCULATE((DISTINCTCOUNT(DS_main_for_usa[CALENDAR_DATE])) )))

Unique Days V2 =

SUMX(ADDCOLUMNS(SUMMARIZE(DS_main_for_usa, DS_main_for_usa[ENGAGEMENT_DIVISION_NAME],

DS_main_for_usa[CUSTOMER_NAME],DS_main_for_usa[ENGAGEMENT_NAME],DS_main_for_usa[TASK_NAME],

DS_main_for_usa[ENGAGEMENT_STATUS]),"MAXDays", MAXX(DS_main_for_usa, DS_main_for_usa[Unique Days])),[MAXDays])

Can anyone help? My formula for Unique Days V2 is returning 4 and not 3 for some reason.

Thank you!!

Re: Need Help Summing a Distinct Count in a Matrix Across all Rows

Mahesh0016 — Fri, 16 Jun 2023 06:11:42 GMT

DTGPete I hope this helps you!! Thank You!!
Unique Days V2 =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
DS_main_for_usa,
DS_main_for_usa[ENGAGEMENT_DIVISION_NAME],
DS_main_for_usa[CUSTOMER_NAME],
DS_main_for_usa[ENGAGEMENT_NAME],
DS_main_for_usa[TASK_NAME],
DS_main_for_usa[ENGAGEMENT_STATUS]
),
"MAXDays", MAXX ( VALUES ( DS_main_for_usa ), DS_main_for_usa[Unique Days] )
),
[MAXDays]
)

##############################ELSE###############################

Unique Days V2 =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
DS_main_for_usa,
DS_main_for_usa[ENGAGEMENT_DIVISION_NAME],
DS_main_for_usa[CUSTOMER_NAME],
DS_main_for_usa[ENGAGEMENT_NAME],
DS_main_for_usa[TASK_NAME],
DS_main_for_usa[ENGAGEMENT_STATUS]
),
"MAXDays", MAXX ( VALUES ( DS_main_for_usa[Unique Days] ), DS_main_for_usa[Unique Days] )
),
[MAXDays]
)

Re: Need Help Summing a Distinct Count in a Matrix Across all Rows

DTGPete — Fri, 16 Jun 2023 13:08:57 GMT

Mahesh0016 - thank you so much for answering my question. Unfortunately, that meaure still returns a value of 4 instead of 3. Any ideas?

Re: Need Help Summing a Distinct Count in a Matrix Across all Rows

DTGPete — Fri, 16 Jun 2023 14:13:27 GMT

Okay I was making this way more complicated than needed.

The following measure solved my issue:

Unique Days = CALCULATE(DISTINCTCOUNT(DS_main_for_usa[CALENDAR_DATE]),DS_main_for_usa[UNIQUEDAYS]<>BLANK())