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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

New Users measure

Hi, 

I have a table where I have all the logins for each user, one line is one login.

I have a calculated column retrieving the registration date from another table via LOOKUPVALUE. I made this measure to be able to filter the data and see who are the "New" users for a specific month. A "New" user is defined as someone who has the registration date in a specific month. I am then filtering the dates with one or more months.

 

I created this that works but not in all cases, someone can help to debug it?

 

New Users Measure= 
            VAR NewUsers =
            FILTER (Table_Visitors,
                CONTAINS ( VALUES ( 'calendar'[Date] ), 'calendar'[Date], Table_Visitors[Registration Date] )
            )
        RETURN
        COUNTROWS(SUMMARIZE(NewUsers,Table_Visitors[Visitor ID]))

 

Thank you for your help!

1 ACCEPTED SOLUTION

Hi @Anonymous ,

Sorry for late back. Yes, for multiple lines in different month it also works. If in the same month there're multiple lines and you want to count all the lines, change the formula to:

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table_Visitors[Visitor ID] ),
    FILTER (
        'Table_Visitors',
        COUNTROWS (
            FILTER ( 'Table_Visitors', 'Table_Visitors'[Month] = MAX ( 'calendar'[Month] ) )
        ) > 0
            && COUNTROWS (
                FILTER (
                    'Table_Visitors',
                    'Table_Visitors'[Month]
                        = MAX ( 'calendar'[Month] ) - 1
                )
            ) = 0
    )
)

Best Regards,
Community Support Team _ kalyj

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

View solution in original post

8 REPLIES 8
Josh_Boj
Advocate I
Advocate I

It would be nice to have one location to manage all users

https://ideas.powerbi.com/ideas/idea/?ideaid=e413d9e7-f785-ed11-a76e-281878bd14b2

v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

According to your description, I create a sample.

Table_Visitors:

vkalyjmsft_0-1663236294575.png

Calendar table:

vkalyjmsft_1-1663236314571.png

The two tables are related with date columns.

vkalyjmsft_2-1663236351482.png

Create a measure.

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table_Visitors[Visitor ID] ),
    FILTER (
        'Table_Visitors',
        COUNTROWS (
            FILTER ( 'Table_Visitors', 'Table_Visitors'[Month] = MAX ( 'calendar'[Month] ) )
        ) > 0
            && COUNTROWS (
                FILTER (
                    'Table_Visitors',
                    'Table_Visitors'[Month]
                        = MAX ( 'calendar'[Month] ) - 1
                )
            ) = 0
    )
)

Get the result.

vkalyjmsft_3-1663236424725.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

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

Anonymous
Not applicable

Thank you @v-yanjiang-msft but in my Table_Visitors the same visitor can be in multiple lines (multiple logins), even though the registration date would be the same. Does your measure work also in this case?

Hi @Anonymous ,

Sorry for late back. Yes, for multiple lines in different month it also works. If in the same month there're multiple lines and you want to count all the lines, change the formula to:

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table_Visitors[Visitor ID] ),
    FILTER (
        'Table_Visitors',
        COUNTROWS (
            FILTER ( 'Table_Visitors', 'Table_Visitors'[Month] = MAX ( 'calendar'[Month] ) )
        ) > 0
            && COUNTROWS (
                FILTER (
                    'Table_Visitors',
                    'Table_Visitors'[Month]
                        = MAX ( 'calendar'[Month] ) - 1
                )
            ) = 0
    )
)

Best Regards,
Community Support Team _ kalyj

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

Anonymous
Not applicable

@v-yanjiang-msft it is the same formula, I don't want to double count someone if they are having 2 logins for that month. How do I edit the formula? At the moment if someone is a new user in January for example but they have 2 logins it shows 2 instead of 1.

 

Hi @Anonymous ,

Do you mean only count twice for user in there registrate month? 

For ex, a user registrate at January, if he login twice in January, it counts twice. But if he login in twice in February, it only counts once?

Best Regards,
Community Support Team _ kalyj

MFelix
Super User
Super User

Hi @Anonymous ,

 

Without any data is difficult to understand where your measure is breaking.

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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