Forum Discussion

OscarCamean's avatar
OscarCamean
Regular Visitor
2 years ago
Solved

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 [mes...
  • Anonymous's avatar
    Anonymous
    2 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.