topic Re: Frequency between dates in DAX Commands and Tips

Frequency between dates

Anonymous — Mon, 11 Jul 2022 14:54:54 GMT

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.

Re: Frequency between dates

Jihwan_Kim — Mon, 11 Jul 2022 17:42:10 GMT

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.

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

Re: Frequency between dates

Anonymous — Tue, 12 Jul 2022 08:05:11 GMT

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

Re: Frequency between dates

Jihwan_Kim — Tue, 12 Jul 2022 12:04:18 GMT

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.