Forum Discussion

Anonymous's avatar
Anonymous
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
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.

 

  • Anonymous's avatar
    Anonymous
    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

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 26, 2017

      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

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Jul 26, 2017
      You could try

      Entity orders :=CALCULATE(Raw_Data[Sum of Orders] ,
      FSA_Role[P&L]="Entity")
      • Anonymous's avatar
        Anonymous
        Jul 26, 2017

        tested but not working. giving me an error 

Resources