Forum Discussion

obothewizard's avatar
obothewizard
Helper I
5 years ago
Solved

Count users based on same day activity

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
 
 

 

 

 

 

Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

     

8 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      obothewizard
      Helper I
      5 years ago

      oh wow, that works perfectly! Thank you so much for your time and help ğŸ˜Š

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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 

     

    • obothewizard's avatar
      obothewizard
      Helper I
      5 years ago

      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's avatar
        obothewizard
        Helper I
        5 years ago

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

      • AlB's avatar
        AlB
        Community Champion
        5 years ago

        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