Count users based on same day activity

[obothewizard]

Hi there, 

I am trying to write a measure which will pull in a count of all users by day who have made an order and also logged into wifi. The order and wifi tables are separate and are linked with a date table.

I am using the below measure  to count Order customers who are also registered in the wifi table

Order & Wifi = COUNTROWS(FILTER('Order',CONTAINS('Wifi Signup',[ID],'Order'[ID])))

However when I apply this measure to a chart using the date table the counts don't add up. For example the Order & Wifi measure used in a card for January 2020 would be 62, but when I use this measure in a January column chart the daily figures are much lower and come to a total of 42 across all days in Jan

I don't understand why the numbers are different. I simply want to know the daily volumes of wifi and order customers in a chart and for the card to represent the total volume of distinct order and wifi customers. I would have though they would tally as the same irrespective of the visual but I am not seeing this.

I would be super grateful for any help or guidance in this matter.

Cheers

Eve

[AlB · Accepted solution]

@obothewizard 

You need the evaluate the measure date by date to replicate what is happening in the chart. Use this measure (that uses the one you have already) in the chart and in the card:

Same Day Order & Wifi  TOT = 
SUMX( DISTINCT('Date Table'[Date]), [Same Day Order & Wifi])

 Note there is no need for the CALCULATE in [Same Day Order & Wifi]. You can get rid of it

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[obothewizard]

oh wow, that works perfectly! Thank you so much for your time and help 😊

[AlB]

Hi @obothewizard 

Can you share the pbix (with dummy data reproducing the issue if necessary) or show samples of your relavant data?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@obothewizard 

It loosk liek the linke requires signing in. Please remove the requirement or host it elsewhere....or just upload the file to a site like tinyupload.com (no sign-up required).

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[obothewizard]

Thanks for the tip, does this link work?

http://s000.tinyupload.com/index.php?file_id=37093313513870933083

[obothewizard]

Hi there,

Thanks so much for your reply.

I've attached a pbix as requested. Essentially I need a distinct count of customers per day who appear in both the order and wifi logins tables on the same date - so need to write a measure that matches on ID and date. 

In the workbook, the  bar chart shows daily vols of wifi with orders customers for January 2020, these are the correct figures. However the card beneath which uses the same measure is showing the wrong value - I did some spot checking of the data in excel and the count should be 44 custs not 62 as shown in the card. The daily volumes add up to 44 in the chart, but when I expand the date drilldown to monthly, it then reflects 62 which is wrong.

Please let me know if you need anymore info, and thanks very much for your help

[obothewizard]

Sorry how do I attach the pbix workbook? Can't see any obvious button!