Count the number of occurrences of an ID

[jackj]

Hi,

I'm looking to develop the following measures and am struggling to put everything together:

1) A distinct count of User ID's from a data table ('daily orders'), filtered by whether or not that User ID appears during a particular month (selected by a slicer).

2) A distinct count of User ID's from 'daily orders', filtered by whether or not that User ID appears during a particular week (selected by a slicer).

3) A calculation dividing the unique count in part 1 above by the unique count in part 2 above, basically showing the percentage of User ID's from part 1 that appeared in part 2.

Could anyone assist with this?  Thanks!!

[jackj]

PBIX file located here: https://drive.google.com/file/d/11T7b8qArjSvBU_0i-kFhFdUwL5y9KNk5/view?usp=sharing

[mhossain]

@jackj 

See that attached pbix.

--Created a dax table for Date_Month

--M1 and M2 measures as per your point 1 & 2

--then finally M2 divided by M1 to get the %

[jackj]

Thank you so much!!  I reviewed the DAX file, and I think I may have misphrased my steps.  Essentially, I am looking to calculate the count of distinct users who visited during a selected week, and then be able to choose any other time period in a separate slicer to count how many of that group of selected users returned.

For example, in your PBIX, if I select 6/13/20 as FW EndOfWeek, I get 14 distinct users.  I want to be able to have a second slicer that I can select that will give me the number of those 14 users who returned in the Month selected in slicer #2.  So if I select July 2020 in slicer #2, the result should be that 11 of the 14 users returned.  I would also like to have a card that dynamically updates to show 11/14 = 78.57% for this measure, but will change based on the time periods selected.

Hope this clarifies, thank you again so much!!

[v-lionel-msft]

Hi @jackj ,

"For example, in your PBIX, if I select 6/13/20 as FW EndOfWeek, I get 14 distinct users.  I want to be able to have a second slicer that I can select that will give me the number of those 14 users who returned in the Month selected in slicer #2.  So if I select July 2020 in slicer #2, the result should be that 11 of the 14 users returned"

I studied your data, if [FW EndOfWeek] = 6/13/20 and [MM YYYY] = July 2020, the statistic value is blank. 

Try to refer to my .pbix file, hope this is what you want.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jackj]

I reviewed the PBIX and I'm not sure it's doing what I intend here.  For the 13 unique user ID's from 6/25/20, I want to be able to select any other date (or week, or month) in another slicer and count how many of those 13 user ID's appeared again on that date, and calculate the percentage.  I believe the slicers would need to be independent from each other in order to do that.

[mhossain]

@jackj 

Please share the powerbi with dummy data and your slicers if possible.