Frequency between dates

[Anonymous]

Hi,

I am a basic BI user and need help with a measure.

I would also like an explanation of what you are doing so I can learn 🙂

I have a table of AAD logins.

Each login has a userID, loginCountry, loginDate.

I am looking to produce a table that shows for each loginCountry, how many high-frequency-logins (5 logins within 2 days) occured and when the first and last of thise were.

I appreciate you teachers.

[Jihwan_Kim]

HI,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

I assume the requirement is to find how many users are the high-frequency-login (more than five per each country) between two dates.

Please check the below picture and the attached pbix file.

Login count measure: = 
COUNTROWS( Data )

High frequency login users: = 
SUMX (
    VALUES ( Country[Login Country] ),
    CALCULATE (
        COUNTROWS (
            FILTER (
                ADDCOLUMNS ( VALUES ( UserID[User ID] ), "@logincount", [Login count measure:] ),
                [@logincount] > 5
            )
        )
    )
)

[Anonymous]

Hi Kim,

Many thanks for your time here.

This certainly seems like a good start. However, for this report the definiton of 'high frequency' is a user that has logged in 5 times or more at a specific location within a period of ANY 2 days.

I don't know when those 2 days might be, so using a slicer will not work. The measure I am trying to write must return 'users where 5 or more logins were made at an individual country within a 2 day period'.

I would like to see which country, user, date period.

So;

if user A logged in once a day for a week in London - i don't care.

If user A logged in 3 times on Jan 1st in Hawai and then 3 times on the 2nd in Paris - i don't care.

if user A logged in twice on Feb 1st in New York and 3 times on the 2nd in New York - i need to see that

if user A logged in twice on Mar 1st in Mexico and 3 times on the 2nd in Mexico - i need to that aswell

Many thanks again.

Terry

[Jihwan_Kim]

Hi,

Thank you for your message.
Sorry that I do not know how to create a sample that suits your situation, and please share your sample pbix file's link here. And then I can try to look into to it to come up with a more accurate solution.

Thanks.