cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Recurrence in DAX with SUMMARIZE AND FILTER

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

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

VAR UsuariosRecurrentes =
SELECTCOLUMNS(
FILTER(
SUMMARIZE(
[UserId],
"MesesConVisitas", COUNTROWS(
FILTER(
[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!

1 ACCEPTED SOLUTION
Community Support

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

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(
[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.

5 REPLIES 5
Community Support

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

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(
[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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
New Member

Hi!

I have edited the post with some data.

Thanks!!

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

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

VAR UsuariosRecurrentesPorMes =
SUMMARIZE(
[UserId], [EndOfMonth]
),
"CumpleCondicion",
IF(
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

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors