Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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])) )))
Can anyone help? My formula for Unique Days V2 is returning 4 and not 3 for some reason.
Thank you!!
Solved! Go to Solution.
Okay I was making this way more complicated than needed.
The following measure solved my issue:
@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]
)
@Mahesh0016 - thank you so much for answering my question. Unfortunately, that meaure still returns a value of 4 instead of 3. Any ideas?
Okay I was making this way more complicated than needed.
The following measure solved my issue:
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
69 | |
36 | |
21 | |
17 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
23 |