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

     

     

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