Dynamic filtering / issue in column

[cmce2019]

Video: https://www.youtube.com/watch?v=iLxiBOCoLEE

Hi,

I have a test data set and my issue is when i try to dynamic id assign the columns "relid" and "relparentid" by filters

i will explain it, i want to show a organizacional chart, like this:

I want the following chart to show by a filter for example by "Engineering" 

 

But the issue is when i use a BI filter, because i need the columns "relid" and "relparentid" to recalculate when using the filter, otherwise it will show this up

Therefore the chart won't load, because when I'm filtering I need a root, and as showed in the previous image every single item has a parent id, so when filtering this parent id doesn't exist. So I need the list to be calculated in the following way.

I did this in Excel, then I transferred the data into Power BI, but I need to do it solely in Power BI

pdta: 

This video shows how I have the Excel file, and trying to load it into Power BI

Hopefully you can help me and thank you.

[Greg_Deckler]

@cmce2019 - Well, if you need it to recalculate then it will need to be a measure, not a calculated column. What custom visual are you using for this? Have you checked out the PATH family of functions? Sample data posted as text would help.

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[cmce2019]

I'm using swOrgChart, that visual does not supports measures

[Anonymous · Accepted solution]

Hello @cmce2019 ,

Data from the child layer in the organization chart can only be presented after the data in the parent layer to be displayed. Therefore, you can select Management when you select the engineering department at the same time. Similarly, if you want to display sales department information, also select the management department. In addition, you can also do this by creating a department dimension table and a measure. I created a sample file for you, you can check if that's what you want.

Note: Open the sample file with the latest version of Power BI Desktop.

Choose multiple departments Create department dimensiontable and measurethe best greetings

Rena

[cmce2019]

You rock, thank you.

[amitchandak]

@cmce2019 , not very clear to me,

Have you tried this way

https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-ch...

https://www.thebiccountant.com/2019/10/03/parent-child-hierarchies-with-multiple-parents-in-power-bi...

[cmce2019]

Basically, I need to re calculate "relid" and "relparentrelid" according to the filter used