Forum Discussion

Deleted's avatar
Deleted
Jul 20, 2017

Table Relationship

Hello I have a table in powerpivot data model two tables linked to each other through a one to many relationship. the link is dne through the key "Role" In the one side table I have put two colums ...
BI & Data Analytics
Need Help
Wyn Hopkins's avatar
Wyn Hopkins
MVP
Jul 21, 2017
Hi Imed

When you say you can't implement this do you mean it won't let you join the 2 tables together?

Deleted's avatar
Deleted
Jul 21, 2017

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,

 

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Jul 21, 2017
    You will need to list all roles and state each one is Destination. Any roles not mapped will show as Blank

    One option would be to do the mapping using Merge Queries in Power Query and then replace all the nulls with Destination
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 21, 2017

      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.

       

      • Deleted's avatar
        Deleted
        Jul 26, 2017

        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

         

Resources