Recurrence in DAX with SUMMARIZE AND FILTER

[OscarCamean]

I have a problem in DAX when displaying a metric in an area chart. The goal is to represent unique users who have performed a number of actions equal to or greater than [n_veces] during the last [meses] consecutively.

TotalUsuariosRecurrentes = 

VAR SELECTOR = IF([Recurrencia numerico] = 365,12,[meses])
VAR SELECTOR_veces = [n_veces]

VAR hoy        = TODAY()
VAR FechaIni   = EDATE(hoy,SELECTOR * -1)
VAR FechaFin   = hoy

VAR ResumenUsuarios = 
    SUMMARIZE(
        FILTER(
            Tabla,
            Tabla[EndOfMonth] >= FechaIni && Tabla[EndOfMonth] <= FechaFin
        ),
        Tabla[UserId],
        Tabla[EndOfMonth],
        "SumaVisitas", SUM(Tabla[N_VISITAS])
    )

VAR UsuariosRecurrentes = 
    SELECTCOLUMNS(
        FILTER(
            SUMMARIZE(
                ResumenUsuarios,
                [UserId],
                "MesesConVisitas", COUNTROWS(
                    FILTER(
                        ResumenUsuarios,
                        [UserId] = EARLIER([UserId]) &&
                        [SumaVisitas] >= SELECTOR_veces
                    )
                )
            ),
            [MesesConVisitas] >= SELECTOR
        ),
        "UserId", [UserId]
    )

RETURN
    CALCULATE(
        DISTINCTCOUNT(Tabla[UserId]),
        FILTER(
            Tabla,
            Tabla[UserId] IN UsuariosRecurrentes &&
            Tabla[EndOfMonth] >= FechaIni && Tabla[EndOfMonth] <= FechaFin
        )
    )

To help you understand the logic I am using in the metric I will explain the following parameters:

1.[n_veces] = number of actions (int)

2.[meses] = number of months (int)

3.[Recurrencia numerico] = if recurrence slicer has de value "Mensual" selected, then 30, else 365 (int)

Those parameters filters the table shown below and should also filter the area visualization located just below the table.

This is the how the data looks like:

EndOfMonth	UserId	WorkspaceId	ReportId	N_VISITS
31/07/2024	User1@domain	1234a	2a	2
31/07/2024	User1@domain	1234a	1a	7
31/07/2024	User2@domain	1234a	2a	2
31/07/2024	User2@domain	1234a	1a	1
31/07/2024	User3@domain	1234a	1a	2
31/07/2024	User5@domain	1234a	1a	2
31/07/2024	User4@domain	1234a	3a	1
30/06/2024	User1@domain	1234a	3a	1
30/06/2024	User2@domain	1234a	1a	2
30/06/2024	User3@domain	1234a	2a	3
30/06/2024	User4@domain	1234a	3a	5
31/05/2024	User1@domain	1234a	2a	12
31/05/2024	User2@domain	1234a	1a	2
31/05/2024	User3@domain	1234a	1a	4
31/05/2024	User4@domain	1234a	3a	5

The problem is that, when I select a number of month ([meses]) greater than 1, it stop showing the visualization when it should show, in this specific case the last 3 months (X axis) with just 1 user (Y axis) as is the only one who have done at least 2 actions each month during the last 3 months:

EndOfMonth	Users
30/07/2024	1
30/06/2024	1
31/05/2024	1

I expect to be able to show an evolution on this metric having the date field on X axis, now it can only be displayed on a KPI visualization.

As the data have more detail that the required for the metric (EndOfMonth, UserId, WorkspaceId, ReportId, Visits), I have tried to do a summarized table on a variable to calculate the users with the conditions selected (UsuariosRecurrentes) and then tried to insert that list of users on the calculate after the return statement.

The result should look like this:

First, the list of users who meet the conditions of total number of visits per month ([n_veces]) each of the last number of months  consecutively ([meses]):

UserId	(VAR UsuariosRecurrentes)
User1@domain	1
User2@domain	1
User3@domain	1
User4@domain	1

Then, on the original table each ocurrence of each recurring users, each EndOfMonth inside the number of month ([meses]) parameter:

EndOfMonth	UserId	(Metric) TotalUsuariosRecurrentes
31/07/2024	User1@domain	1
31/07/2024	User2@domain	1
31/07/2024	User3@domain	1
31/07/2024	User5@domain	0
31/07/2024	User4@domain	1
30/06/2024	User1@domain	1
30/06/2024	User2@domain	1
30/06/2024	User3@domain	1
30/06/2024	User4@domain	1
31/05/2024	User1@domain	1
31/05/2024	User2@domain	1
31/05/2024	User3@domain	1
31/05/2024	User4@domain	1

Any help on this?

Thanks a lot!

[v-xianjtan-msft · Accepted solution]

Hi @OscarCamean 

The problem may be in the way the data is filtered and summarized, here is an idea for improvement, hope it is helpful.
Since I don't know all the fields of your table, [Meses] [N_veces] [RecurrenciaNumerico] in my example are from manually entered data.
If this DAX doesn't work, please provide dummy data for all fields used in your DAX and remember to protect your privacy.
Furthermore, please consider breaking your DAX into multiple parts and debugging it step by step.

1. Here is my sample data.

   

2. Create measures to get the value selected by the slicers.

Selected_Meses = SELECTEDVALUE(meses[meses])

Selected_N_Veces = SELECTEDVALUE(n_veces[n_veces])

RecurrencialNumerico = SWITCH(
    TRUE(),
    SELECTEDVALUE(Recurrencia[Frequency]) = "Year", 365,
    SELECTEDVALUE(Recurrencia[Frequency]) = "Month", 30,
    SELECTEDVALUE(Recurrencia[Frequency]) = "Day", 1,
    0
)

3. Create [TotalUsuariosRecurrentes] measure.

TotalUsuariosRecurrentes = 

VAR SELECTOR = IF([RecurrencialNumerico] = 365,12,[Selected_Meses])
VAR SELECTOR_veces = [Selected_N_Veces]

VAR hoy        = TODAY()
VAR FechaIni   = EDATE(hoy,SELECTOR * -1)
VAR FechaFin   = hoy

VAR ResumenUsuarios = 
    SUMMARIZE(
        FILTER(
            Tabla,
            Tabla[EndOfMonth] >= FechaIni && Tabla[EndOfMonth] <= FechaFin
        ),
        Tabla[UserId],
        Tabla[EndOfMonth],
        "SumaVisitas", SUM(Tabla[N_VISITAS])
    )

VAR UsuariosRecurrentes = 
    CALCULATETABLE(
        DISTINCT(Tabla[UserId]),
        FILTER(
            ResumenUsuarios,
            [SumaVisitas] >= SELECTOR_veces
        )
    )

RETURN
    CALCULATE(
        DISTINCTCOUNT(Tabla[UserId]),
            Tabla[UserId] IN UsuariosRecurrentes &&
            Tabla[EndOfMonth] >= FechaIni && Tabla[EndOfMonth] <= FechaFin
        )

4. Here is my test result.

Best Regards,

Jarvis Tang

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

[Ashish_Mathur]

Hi,

Share some data to work with (in a form that can be pasted in an MS Excel file) and show the expected result in a simple Table format.  If possible, please ensure the column names are in English. 

[OscarCamean]

Hi!

I have edited the post with some data.

Thanks!!

[Ashish_Mathur]

Hi,

Share some data to work with (in a form that can be pasted in an MS Excel file) and show the expected result in a simple Table format.  If possible, please ensure the column names are in English. 

[rajendraongole1]

Hi @OscarCamean - The filtering logic accounts for each month individually and correctly sums up the users who meet the criteria and return the number of unique users per month who meet the criteria.

check the below measure:

TotalUsuariosRecurrentes =
VAR SELECTOR = IF([Recurrencia numerico] = 365, 12, [meses])
VAR SELECTOR_veces = [n_veces]

VAR hoy = TODAY()
VAR FechaIni = EDATE(hoy, SELECTOR * -1)
VAR FechaFin = hoy

VAR ResumenUsuarios =
SUMMARIZE(
FILTER(
Tabla,
Tabla[EndOfMonth] >= FechaIni && Tabla[EndOfMonth] <= FechaFin
),
Tabla[UserId],
Tabla[EndOfMonth],
"SumaVisitas", SUM(Tabla[N_VISITAS])
)

VAR UsuariosRecurrentesPorMes =
ADDCOLUMNS(
SUMMARIZE(
ResumenUsuarios,
[UserId], [EndOfMonth]
),
"CumpleCondicion",
IF(
CALCULATE(SUM([SumaVisitas]), ResumenUsuarios) >= SELECTOR_veces,
1,
0
)
)

VAR UsuariosRecurrentes =
SUMMARIZE(
FILTER(
SUMMARIZE(
UsuariosRecurrentesPorMes,
[UserId],
"MesesConVisitas", SUM([CumpleCondicion])
),
[MesesConVisitas] >= SELECTOR
),
[UserId]
)

RETURN
CALCULATE(
DISTINCTCOUNT(Tabla[UserId]),
FILTER(
Tabla,
Tabla[UserId] IN UsuariosRecurrentes &&
Tabla[EndOfMonth] >= FechaIni && Tabla[EndOfMonth] <= FechaFin
)
)

This step adds a column to indicate whether the user meets the condition of having >= [n_veces] actions in that month.

Hope it works . please check