Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unique records per month over series of months

Hi,

I have a table with a user ID and all the dates a user logged in

 

User ID

Log in date

A

Jan 1 2019

A

Jan 2 2019

A

Feb 20 2019

A

Mar 10 2019

A

Apr 15 2019

B

Jan 5 2019

B

Mar 6 2019

B

Apr 12 2019

 

I’m now looking for a measure that will show me the amount of unique users that logged in at least once per month over a series of months that I define using a filter on the page.

 

Example:

I set the filter on the page to April 2019. The measure should then show me that there’s 2 users (A and B) that logged in once for April.

I set the filter on the page to January, February, March, and April 2019. User A logged in every month over that time period. User B only logged in in January March and April, and NOT in February. So in this case the measure should return 1 as only user A logged in at least once for all of those months.

 

So as you can see a simple distinctcount wouldn’t work here. What measure should I use to get the desired outcome?


Thanks in advance!

5 REPLIES 5
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

Please see the below, let me know if this is what your after.

Measure = 
CONCATENATEX(
    VALUES(YourTable[User ID]),
    YourTable[User ID],
    ", "
)

Regards,
Mariusz

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

Anonymous
Not applicable

Hi @Mariusz ,

Thank you for helping out. your calculation is returning an enormous long metric that looks like this

A, B, C, D, E, F

 

I need to know the unique amount of ID's that are in that metric, do you know how I can get that number?

Regards

Bas

Hi @Anonymous 

Not sure what you want to achieve, can you illustrate the desired output?

Thanks
Mariusz

Anonymous
Not applicable

Hi @Mariusz 

Sure thing.

 

1) I want this measure to show me the amount of users that logged in at least once per month between january and april 2019.

User A logged in in January, February, March, and April, and therefore counts as 1

User B logged in in January, March, and April. He did not log in in February. He therefore doesnt count.

 

In total I have 1 user that logged in at least once per month between january and april 2019, the measure should return 1.

 

2) I want the same measure to show me the amount of users that logged in at least once per month between March and April. 

User A logged in in March and April and therefore counts as 1

User B logged in in March and April and therefore counts as 1

 

In total I have 2 users that logged in at least once per month between March and April, the measure should return 2.

 

Does this make sense?

Best regards and thanks again for looking into this!

Bas

 

 

Hi @Anonymous 

Working on assumption that you have Year Month column in your table, the code should look something like below.

Measure = 
VAR ym = DISTINCTCOUNT( YourTable[YearMonth] ) 
RETURN 
COUNTROWS(
    FILTER( 
        VALUES( YourTable[User ID] ), 
        CALCULATE( DISTINCTCOUNT( YourTable[YearMonth] ) ) = ym 
    )
)


Regards,
Mariusz

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.