Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count Distinct by Date

Hi,

 

I want to find the distinct count of customers who have visited a shop everyday. Is there a specific DAX or Mquery?

My plan is to create a column chart to find unique customers per day grouped monthly.  

 

Eg: "Sam" visits Wmart today thrice and tomorrow twice. I want him to be counted once today and again counted once tomorrow. Similarly for all customers and then add the values monthly.

 

Would appreciate if anyone could show me how to do it.

 

Regards,

Ruban 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous ryan_mayu

    Eg: "Sam" visits Wmart today thrice and tomorrow twice. I want him to be counted once today and again counted once tomorrow. Similarly for all customers and then add the values monthly.

    So times of Sam visits Shop these two days is 2, not just distinctcount result is 1.

    You could try to use DISTINCTCOUNT and SUMX to create a measure like this

    Measure 2 = var _table=SUMMARIZE('Table','Table'[Shop],'Table'[Date],"distinctcount",DISTINCTCOUNT('Table'[Customer]))
return
SUMX(_table,[distinctcount])

    Result:

    Regards,

    Lin

     

     

6 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous ryan_mayu

    Eg: "Sam" visits Wmart today thrice and tomorrow twice. I want him to be counted once today and again counted once tomorrow. Similarly for all customers and then add the values monthly.

    So times of Sam visits Shop these two days is 2, not just distinctcount result is 1.

    You could try to use DISTINCTCOUNT and SUMX to create a measure like this

    Measure 2 = var _table=SUMMARIZE('Table','Table'[Shop],'Table'[Date],"distinctcount",DISTINCTCOUNT('Table'[Customer]))
return
SUMX(_table,[distinctcount])

    Result:

    Regards,

    Lin

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks Lin. Sorry for the delay in response. Your formula worked.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello v-lili6-msft,

       

      To go further, is it possible to calculate an average of this measure. A weekly average taking into account the working days

       

      thank you

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous can you provide sample data and will get you the solution

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    7 years ago

    Anonymous 

     

    Hi, why distinctcount formula doesnot work in this situation? Have you tried about that? Or you have some specific requirement?

    Hope this is helpful.

     

    Thanks and BR

    Ryan

  • fsadiq's avatar
    fsadiq
    Frequent Visitor
    4 years ago
    Hello All, I have a list of dates and Names. Each name has random dates based on their daily activities i.e A name has like 3 same date because he/she carried out 3 activities that same day . I already sorted the datasets by month using the slicer viz. I want to count all unique dates in a month i.e When you select may, I want to have unique number of days in may using Card Viz. Viz: https://ibb.co/sCm2dkP Table: https://ibb.co/1JqhgRR I made the Month sorting using end column. Thanks