Count Distinct Not Working

[Anonymous]

Hello, 

I am having trouble counting the number of distinct weeks related to the following employee while ignoring the partition on "Reason Name" (shown in the column headers ex. "Work", "Partner", "Travel", etc.).   I've tried several methods to no avail. 

I first tried this..

 CALCULATE(

         COUNTROWS(DISTINCT('Avg Weekly CRM Hours by Tech/Week/Reason'[Week Start Date]))

        , ALLSELECTED('Avg Weekly CRM Hours by Tech/Week/Reason'[Reason Name])

)

 Then tried this... 

CALCULATE(

    COUNTROWS(

        DISTINCT(

        SELECTCOLUMNS('Avg Weekly CRM Hours by Tech/Week/Reason'

         , "Week Start Date"

        , 'Avg Weekly CRM Hours by Tech/Week/Reason'[Week Start Date]

        )))

         , ALLEXCEPT('Avg Weekly CRM Hours by Tech/Week/Reason','Avg Weekly CRM Hours by Tech/Week/Reason'[Reason Name]

         ))

Neither option worked

[Ashish_Mathur]

Hi,

You should first create a Calendar Table and then build a relationship (Many to One and Single) from the Week start date column to the Date column of the Calendar Table.  To your visual, drag Month name and Date from the Calendar Table.  Write these measures

Total = distinctcount('Avg Weekly CRM Hours by Tech/Week/Reason'[Week start date])

Measure = calculate([Total],all(calendar[Date]))

Hope this helps.