Using relationship from a virtual table to a Dimension Table

[Anonymous]

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

I add the data

[Anonymous]

[Anonymous]

I cant do a direct relationship between two tables because type user in User_type table is dinamic and depend on the filter data

[Anonymous · Accepted solution]

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 ¡¡¡¡