Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Using relationship from a virtual table to a Dimension Table

Hi, I have a virtual table agregada con User y User_Type ... and I have a dimension table about User_Type

Usuario      Tipo de usuario

Usuario1         Tipo1

Usuario2         Tipo1

Usuario3         Tipo3

I cant do a normal relationship between User Table and User_Type Dimension Table because the user_Type can change depending from date filter.

I have buid the follow code:

 

Recu =

VAR MinDate = SELECTEDVALUE(Calendario[Fecha], MIN(Calendario[Fecha]))
VAR MaxDate = SELECTEDVALUE(Calendario[Fecha], MAX(Calendario[Fecha]))
VAR T1 =  
            GROUPBY(
                ADDCOLUMNS(
                SUMMARIZECOLUMNS(Sesiones[user_Id], Sesiones[session_Id], Sesiones[Timestamp], Sesiones     [FechaUsuario], "F1", MaxDate, "Dias", int(MaxDate- MIN(Sesiones[FechaUsuario]))),
                    "Tipo", IF([Dias]<31,"First_Time", IF(AND([Dias]>=31,[Dias]<61), "Previous_Period","Old_Users"))),Sesiones[user_Id], [Tipo], Sesiones[FechaUsuario], [F1])

RETURN
    CALCULATE(DISTINCTCOUNT(Sesiones[user_Id]), FILTER(T1, Sesiones[FechaUsuario]<=[F1]),
                'Sesiones'[Timestamp]>= MinDate && 'Sesiones'[Timestamp]<= MaxDate
)
 
T1 does work well, It adds well the virtual table.
The calculate, does work well too, because it calculates well the number of users.
My problem is when I want to show this kind of chart:

I have 4 users in my case ... Like I cant do a relationship between user table and user_type dimension table because user_table is T1 that is a virtual table ... The chart told me that exist 4 users for each user_type.

This will resolve with a relationship but I have proved a lot of thinks (Treatas, ... ) but it doesnt work anything

 

Some idea ???

 

Thanks

  • Anonymous's avatar
    Anonymous
    2 years ago

    I got it

    I dont need a relationship. I need 3 measures, one measure for each type_status.

     

    Each measure like this:

     

    Recu_First =

    VAR MinDate = SELECTEDVALUE(Calendario[Fecha], MIN(Calendario[Fecha]))
    VAR MaxDate = SELECTEDVALUE(Calendario[Fecha], MAX(Calendario[Fecha]))
    VAR T1 =  CALCULATE(COUNTROWS(
                FILTER(
                        ADDCOLUMNS(
                            SUMMARIZECOLUMNS(Sesiones[user_Id], Sesiones [FechaUsuario], "F1", MaxDate, "Dias", int(MaxDate- MIN(Sesiones[FechaUsuario]))),
                                "Tipo", IF([Dias]<31,"First_Time", IF(AND([Dias]>=31,[Dias]<61), "Previous_Period","Old_Users"))), [Tipo]="First_Time"))
                                    , MaxDate>Sesiones[FechaUsuario])

    RETURN
        T1
     
     
     
     
     
    Recu_Old =

    VAR MinDate = SELECTEDVALUE(Calendario[Fecha], MIN(Calendario[Fecha]))
    VAR MaxDate = SELECTEDVALUE(Calendario[Fecha], MAX(Calendario[Fecha]))
    VAR T1 =  CALCULATE(COUNTROWS(
                FILTER(
                        ADDCOLUMNS(
                            SUMMARIZECOLUMNS(Sesiones[user_Id], Sesiones [FechaUsuario], "F1", MaxDate, "Dias", int(MaxDate- MIN(Sesiones[FechaUsuario]))),
                                "Tipo", IF([Dias]<31,"First_Time", IF(AND([Dias]>=31,[Dias]<61), "Previous_Period","Old_Users"))), [Tipo]="Old_User"))
                                    , MaxDate>Sesiones[FechaUsuario])

    RETURN
        T1
     
     
    Recu_Previous=

    VAR MinDate = SELECTEDVALUE(Calendario[Fecha], MIN(Calendario[Fecha]))
    VAR MaxDate = SELECTEDVALUE(Calendario[Fecha], MAX(Calendario[Fecha]))
    VAR T1 =  CALCULATE(COUNTROWS(
                FILTER(
                        ADDCOLUMNS(
                            SUMMARIZECOLUMNS(Sesiones[user_Id], Sesiones [FechaUsuario], "F1", MaxDate, "Dias", int(MaxDate- MIN(Sesiones[FechaUsuario]))),
                                "Tipo", IF([Dias]<31,"First_Time", IF(AND([Dias]>=31,[Dias]<61), "Previous_Period","Old_Users"))), [Tipo]="Previous_Period"))
                                    , MaxDate>Sesiones[FechaUsuario])

    RETURN
        T1
     
     
     
    And I put on the chart the 3 measures    🙂
     
    Great ¡¡¡ It works ¡¡¡¡

5 Replies