Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Frequency between dates

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.

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Jihwan_Kim
        Super User
        4 years ago

        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.