Forum Discussion

lmh100's avatar
lmh100
New Member
8 years ago
Solved

Count distinct values by date

Hi, 

 

I am trying to create a calculated column in a table - based on counted distinct values that appear during the month - an example of the table is below:

 

DateUnique valueCount requried
31/01/2017A12
31/01/2017B21
31/01/2017A12
31/01/2017B31
31/01/2017B41
28/02/2017A12
28/02/2017A12
28/02/2017B22
28/02/2017B22
31/03/2017A11
31/03/2017B22
31/03/2017B22
31/03/2017C31

 

I have tried CALCULATE(DISTINCTCOUNT( UNIQUE VALUE , UNIQUE VALUE = UNIQUE VALUE, CALENDAR[DATE])

 

But i am just returned with the number rows in that month - rather than the count required column

 

Any help would be appreciated

 

Thanks,

 

Laurence

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi lmh100

     

    Try this column

     

    Count Required =
CALCULATE (
    COUNT ( TableName[Unique value] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[Unique value] ),
        MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) )
    )
)

     

    or this one

     

    Count Required =
CALCULATE (
    COUNT ( TableName[Unique value] ),
    ALLEXCEPT ( TableName, TableName[Unique value], TableName[Date].[Month] )
)

     

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi lmh100

     

    Try this column

     

    Count Required =
CALCULATE (
    COUNT ( TableName[Unique value] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[Unique value] ),
        MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) )
    )
)

     

    or this one

     

    Count Required =
CALCULATE (
    COUNT ( TableName[Unique value] ),
    ALLEXCEPT ( TableName, TableName[Unique value], TableName[Date].[Month] )
)

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    If you just create a Table visualization and drop in your Date column, Unique Value column and then drop in your Date column again and switch the aggregation to Count you will achieve what you are looking for.