Hierarchy and drilldown of tables

[Anonymous]

Hello all,

I have three tables with different information from three different tables from database.

I have one table with shifts, the second table has tasks and the third table has lines.

So if I choose one shift, the other to tables must filter by that shift. And if I choose one task the lines table must filter also by that task.

I have foreign keys in the tables, but in power bi I cannot create the relationship because it says that I need to delete or deactivate some relationships between other tables.

Is there any workaround to implement this hierachy/drilldown between these tables?

Thanks in advance,

Rui

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak 

Thanks for your reply. Basically I have these 3 tables.

If I click in one row of the first one (FactShift table) I want to filter the others, and if I click in one row of the sencond (FactTaskExecution table) the last one (FactTaskDetailExecution table) should filter as well depending on the above one.

All tables have FK to another dim tables (master data).

For example, all three tables have FK to DimOperator table.

I think the problem here is that I cannot create a relationship between FactShift and FactTaskExecution. And also I cannot create a relationship between FactTaskExecution and FactTaskDetailExecution.

It says the error of delete or deactivate relationship between FactTaskExecution and DimVoiceOperator. But if I do that I cannot see the operator name in the table.

Thank you,

Rui

I think the problem here 

[Anonymous]

@amitchandak ,

Sorry to bother, but do you have any idea on this?

Thanks,

Rui

[v-easonf-msft]

Hi,  @Anonymous 

Maybe you can try to create an inactive relationship, and use function "userelationship“ to activate the relationship when creating a measure.

https://www.sqlbi.com/articles/using-userelationship-in-dax/ 

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi 

Best Regards,
Community Support Team _ Eason

[Anonymous · Accepted solution]

Hi @v-easonf-msft,

I have found some workaround. I have created views in SQL that represents those fact tables and I think I can work with them on this case.

I need to perform some more tests but I think it works.

Thanks for your reply and help.

Regards,

Rui