topic Using relationship from a virtual table to a Dimension Table in DAX Commands and Tips

Using relationship from a virtual table to a Dimension Table

Anonymous — Fri, 12 Apr 2024 17:06:51 GMT

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

Re: Using relationship from a virtual table to a Dimension Table

lbendlin — Sat, 13 Apr 2024 17:24:12 GMT

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-Forum/ba-p/963216

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/1447523

Re: Using relationship from a virtual table to a Dimension Table

Anonymous — Mon, 15 Apr 2024 06:41:28 GMT

I add the data

Re: Using relationship from a virtual table to a Dimension Table

Anonymous — Mon, 15 Apr 2024 06:44:44 GMT

Re: Using relationship from a virtual table to a Dimension Table

Anonymous — Mon, 15 Apr 2024 06:47:08 GMT

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

Re: Using relationship from a virtual table to a Dimension Table

Anonymous — Mon, 15 Apr 2024 19:22:49 GMT

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

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

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

And I put on the chart the 3 measures 🙂

Great ¡¡¡ It works ¡¡¡¡