Forum Discussion

DTGPete's avatar
DTGPete
Frequent Visitor
3 years ago
Solved

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

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!!

  • DTGPete's avatar
    DTGPete
    3 years ago

    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())

3 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    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]
    )

    • DTGPete's avatar
      DTGPete
      Frequent Visitor
      3 years ago

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

      • DTGPete's avatar
        DTGPete
        Frequent Visitor
        3 years ago

        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())