Forum Discussion
Recurrence in DAX with SUMMARIZE AND FILTER
- Anonymous2 years ago
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.
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.