Aggregating over date in a measure

[kb26w]

Hello,

I want to build a measure which shows the number of days people login to their account per month, quarter, year. 

The data is in this format - with user ID, login date. I then create end of month and end of quarter in query view and create "Days with a login (month)" and "Days with a login (quarter) as calculated columns using the DAX below. 

Days with a login (month) =

CALCULATE (

    DISTINCTCOUNT ( 'Login activity'[Login date] ),

    ALLEXCEPT (

        'Login activity',

        'Login activity'[User Id],

        'Login activity'[End of Month]

    )

)

Days with a login (quarter) =

CALCULATE (

    DISTINCTCOUNT ( 'Login activity'[Login date] ),

    ALLEXCEPT (

        'Login activity',

        'Login activity'[User Id],

        'Login activity'[End of Quarter]

    )

)

I then do a distinct count of user ID to build the visuals below. 

Can someone help me with the dax to build the measures which allow me to build the above visuals but without the need for multiple visuals and calculated columns? 

So I want to use the dates hierarchy from my dates table on the x-axis (so I can drill up and down) and then build measures for number of people who have logged in on 1 day, 2 days, 3 days etc. So I will have multiple measures which work using my dates table and no calculated columns. 

Thank you in advance 🙂

[DataNinja777 · Accepted solution]

Hi @kb26w ,

If there is a direct relationship between the Login activity table and the Calendar table on the Date column, you can calculate the number of days users log in at various levels of the date hierarchy (month, quarter, year) directly using DAX measures.

To calculate the total number of distinct login days dynamically, you can use the following measure:

Total Login Days = 
CALCULATE(
    DISTINCTCOUNT('Login activity'[Login date])
)

This measure leverages the direct relationship to apply filters from the Calendar table’s date hierarchy to the Login activity table. For instance, if you place the year, quarter, or month from the Calendar table on the x-axis of a visual, this measure will automatically aggregate login days for the selected level.

If you want to calculate how many users logged in on a specific number of days during a selected period, you can use a measure like this:

Users by Login Days = 
VAR LoginDaysSummary =
    SUMMARIZE(
        'Login activity',
        'Login activity'[User ID],
        "LoginDays", DISTINCTCOUNT('Login activity'[Login date])
    )
RETURN
    COUNTROWS(
        FILTER(
            LoginDaysSummary,
            [LoginDays] > 0 -- Adjust this condition as needed for specific ranges
        )
    )

This measure dynamically groups users by the number of days they logged in and counts the users who meet the specified condition. Using these measures with the Calendar table’s hierarchy allows you to create flexible visuals that can drill through different time periods (e.g., month, quarter, year).

The direct relationship between the Calendar and Login activity tables ensures that filters from the date hierarchy are seamlessly applied, making the calculations dynamic and adaptable without unnecessary complexity. This approach avoids the need for calculated columns and enables you to use a single visual to display the required insights at different levels of granularity.

Best regards,

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below, and please check the below picture and the attached pbix file.

In this sample I created a login_count dimension table by writing DAX formula like below.

login_count = 
VAR _maxcount =
    MAXX (
        ADDCOLUMNS (
            VALUES ( login[user_id] ),
            "@logincount", CALCULATE ( COUNTROWS ( login ) )
        ),
        [@logincount]
    )
RETURN
    SELECTCOLUMNS ( GENERATESERIES ( 1, _maxcount, 1 ), "Count", [Value] )

And then, create a measure and put it into the visualization.

ID count by login count: = 
VAR _logincountlegend =
    MAX ( login_count[Count] )
VAR _logincount =
    ADDCOLUMNS (
        VALUES ( login[user_id] ),
        "@logincount", CALCULATE ( COUNTROWS ( login ) )
    )
RETURN
    COUNTROWS ( FILTER ( _logincount, [@logincount] = _logincountlegend ) )

[DataNinja777 · Accepted solution]

Hi @kb26w ,

If there is a direct relationship between the Login activity table and the Calendar table on the Date column, you can calculate the number of days users log in at various levels of the date hierarchy (month, quarter, year) directly using DAX measures.

To calculate the total number of distinct login days dynamically, you can use the following measure:

Total Login Days = 
CALCULATE(
    DISTINCTCOUNT('Login activity'[Login date])
)

This measure leverages the direct relationship to apply filters from the Calendar table’s date hierarchy to the Login activity table. For instance, if you place the year, quarter, or month from the Calendar table on the x-axis of a visual, this measure will automatically aggregate login days for the selected level.

If you want to calculate how many users logged in on a specific number of days during a selected period, you can use a measure like this:

Users by Login Days = 
VAR LoginDaysSummary =
    SUMMARIZE(
        'Login activity',
        'Login activity'[User ID],
        "LoginDays", DISTINCTCOUNT('Login activity'[Login date])
    )
RETURN
    COUNTROWS(
        FILTER(
            LoginDaysSummary,
            [LoginDays] > 0 -- Adjust this condition as needed for specific ranges
        )
    )

This measure dynamically groups users by the number of days they logged in and counts the users who meet the specified condition. Using these measures with the Calendar table’s hierarchy allows you to create flexible visuals that can drill through different time periods (e.g., month, quarter, year).

The direct relationship between the Calendar and Login activity tables ensures that filters from the date hierarchy are seamlessly applied, making the calculations dynamic and adaptable without unnecessary complexity. This approach avoids the need for calculated columns and enables you to use a single visual to display the required insights at different levels of granularity.

Best regards,