Forum Discussion

Andmi's avatar
Andmi
Frequent Visitor
7 years ago
Solved

Hierarchies across multiple linked tables

Hi all,

 

I am in need to create a hierarchy that goes across tables, these tables are linked through 1 to many relationships.

 

The structure I need to have is something like this:

 

-Company Name (in table 1)

--Subcompany (in table 2)

---Type of subscription (in table 2 but the table is linked by a unique company no with table 1)

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi, 

     

    In that case I would suggest for you to do an IF STATEMENT inside which you should place this Lookupvalue. Basically,it can be like

    IF (Parentid《》blank(),lookupvalue function, else block)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

     

    You could use a LookupValue Function in Table 2 to bring in Company column in the Second Table.

    Otherwise, If you are using SSAS, then you can also Create a Hierarchy over there and Import/ Live Data.

    • Andmi's avatar
      Andmi
      Frequent Visitor
      7 years ago

      Anonymous  I have used the formula like this:

       

      Parent Account Name = LOOKUPVALUE(salesforce_account[name];salesforce_account[id];salesforce_account[parentid]; )
       
      But I want to expand it to look at the account name if the parent ID is blank. How can it be done?
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi, 

         

        In that case I would suggest for you to do an IF STATEMENT inside which you should place this Lookupvalue. Basically,it can be like

        IF (Parentid《》blank(),lookupvalue function, else block)

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Andmi ,

     

    If there are existed relationships among these tables, you can create corresponding Company Name column and Type of subscription column using RELATED function, LOOKUPVALUE function, or FIRSTNONBLANK function in Tbale2.

     

    For example:

     

    Column1=RELATED（[one side field of relationship]）

     

    Column1= LOOKUPVALUE(tableA[goal field], tableA[linked field], tableB[linked field]))

     

    Column1= CALCULATE (FIRSTNONBLANK ( tableA[goal field], 1 ),FILTER ( ALL ( Shifts ), tableA[linked field] = tableB[linked field] ))  

     

    Then in Table2, you can create new hierarchy in Field pane. See more detail:https://www.tutorialgateway.org/create-hierarchy-in-power-bi/. (Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.)

     

     

     

     

     

     

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.