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 group...
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

     

     

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