Forum Discussion
Distinct Count by Date
Hi Anonymous ,
Here's my sample data.
DISTINCTCOUNT counts the number of distinct values in a column. If you want to get 10, use COUNT.
Monthcount =
IF (
MAX ( 'Table'[Account ID] ) = 1,
CALCULATE (
COUNT ( 'Table'[Account ID] ),
ALLEXCEPT ( 'Table', 'Table'[Date] )
)
)Daycount =
VAR _sele =
SELECTEDVALUE ( 'Table'[Date].[Month] )
VAR _minD =
MINX ( FILTER ( ALL ( 'Table' ), [Date].[Month] = _sele ), [Date] )
RETURN
IF (
MAX ( 'Table'[Account ID] ) = 1
&& MAX ( 'Table'[Date] ) = _minD,
CALCULATE (
COUNT ( 'Table'[Account ID] ),
ALLEXCEPT ( 'Table', 'Table'[Date].[Month] )
)
)
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The issue is the Distinct Count needs done based on one date (Service Date) and the Filter/Slicer and all other data is done based on another date (Post Date). This cannot be changed.
I think I need a calculated column that counts as a "1" distinct account IDs by the Service Date.. and for repeating lines counts as "0".
Then I can roll up by the existing filter (on Post Date).
Any idea on an equation I could use that uses the Service Date as a criteria to achieve a distinct count?Hi Anonymous ,
Calculated columns will not be affected by slicers or filters. You can only use measures instead.
You can use SELECTEDVALUE function to use another date column B to filter date column A.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.