Forum Discussion

SanderAR's avatar
SanderAR
New Member
9 years ago
Solved

Distinct count based on value and date

Hello,   I have a question about using distinct count based on a number and date in Power Bi.    I have 1 column with accountnumers which have duplicates, column accountnum, I also have dates on ...
  • TomMartens's avatar
    TomMartens
    9 years ago

    Basically it should work.

     

     

     

  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    SanderAR

     

    If you want to calculate the active users per date, you can just create a measure to distinct count accounts group on date level.

     

    Active Users = 
CALCULATE (
    DISTINCTCOUNT ( Table[AccountNum] ),
    ALLEXCEPT ( Table, Table[Date] )
)

    Regards,

     

TomMartens's avatar
TomMartens
Super User
9 years ago

Hey,

 

does this not work:

 

  • Use the date on the x-axis (make sure that the property "Type" of the x-axis is set to categorical)
  • Use the column accountnum in the value section of the visual and set the aggregation to Count (Distinct)

 

 

Hope this helps

SanderAR's avatar
SanderAR
New Member
9 years ago

Hi Tom,

 

Thanks for the fast reply and solution, it seems to work but I still only get the total amount of 5xxx and year and month 

filters don't work, should they? If so it's a relational problem, which I can solve myself. 

 

Thanks for the help in advance!

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    SanderAR

     

    If you want to calculate the active users per date, you can just create a measure to distinct count accounts group on date level.

     

    Active Users = 
CALCULATE (
    DISTINCTCOUNT ( Table[AccountNum] ),
    ALLEXCEPT ( Table, Table[Date] )
)

    Regards,

     

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Basically it should work.