Forum Discussion
Table Relationship
Yes exactly.
what I want to to do is as follow
in table 1 I have a column specifying different roles
Table 2 is defining the role and the type of P&L ( profit and Lost)
I am interested only by some roles for instance
Role_name P&L
Role 1 Entity
Role 2 entity
Role 3 entity
and I would like to specify all other roles should be destination
how to do this.
thank you for your support.
regards,
One option would be to do the mapping using Merge Queries in Power Query and then replace all the nulls with Destination
Another option is - if Table1 defines known roles with second column named "Descr", into Table2 we may add column like
=VAR MyDescr = CALCULATE(VALUES(Table1[Descr]),FILTER(Table1,Table1[Role]=Table2[Role])) RETURN IF(ISBLANK(MyDescr),"Destination",MyDescr)
Relationship between Table1 and Table2 is not required. Not sure about perormance, depends on how big the model is.
Hello
I tested the solution proposed by Segei and noticed that the performance is not that good
so finally I chose the solution to have two tables linked and put the possible roles.
now I hav another issue
im my table Roles, I have two colums
Colum 1 : Roles ( containgna ll possibe rules
Column 2 : Type of P&l tswo possible values either entity or destination
in my Table 2 : Raw_Data
I have different column : role, orders, country, ...
I would like to use a measure that takes into consideration only the roles where the P&L is entity
the system is giving me an error.
I used ralated and if
Entity orders :=CALCULATE(Raw_Data[Sum of Orders] ;
FILTER(Raw_Data; RELATED(FSA_Role[P&L]="Entity")))
can you please support
regards
Hi Imed,
Back to calculation formula. If you add relationship you may improve performance by adding the column like
=IF(ISBLANK(RELATED(Table1[Descr])),"Destination", RELATED(Table1[Descr]))
But you have to check if blank or not in all other places where necessary
