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

     

     

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