Hierarchies across multiple linked tables

[Andmi]

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)

[v-xicai]

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.

[Anonymous]

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]

@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 · Accepted solution]

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)