Forum Discussion
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:
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
- Anonymous2 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])RETURNT1Recu_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])RETURNT1Recu_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])RETURNT1And I put on the chart the 3 measures 🙂Great ¡¡¡ It works ¡¡¡¡
5 Replies
- lbendlin
Super User
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- AnonymousNot applicable
I add the data
- AnonymousNot applicable