Filtering two unconnected tables

[mizaskun]

Good morning,

I have the following shcema in the DB:

Many controls could have many risks and many risks could have many controls. I cannot connect both risk and control tables any other way, and I need add a filter through which the information in the report gets filtered. That is why I tried to create the Area master to use it as the filter input. However, the area master - controls relation gets disabled. 

Is there any way I could achieve this? Getting the info that comes from both tables filtered at the same time.

Thank you so much!! 

[PaulDBrown]

you should be able to use the Risks Controls table to filter both tables if the values correlate (ie they are not a simple list of values unrelated to each other. 

Can you show a depiction  of the actual tables in the model view?

Also please show a depiction of the expected outcome and provide sample data or a PBIX file 

[mizaskun]

Good morning,

The input would be:

The outcome should be the following tables with no filter applied:

If the filter Area = A, the outcome should be:

The filter would be in a dropdown list that depends on the Area table.

Thank you so much @PaulDBrown !

[PaulDBrown · Accepted solution]

Ok, first of all delete the relationships between the tables and the RisksControls table. Now join the Area table to both tables in a 1:N relationship single direction with the corresponding fields in the tables. (I have also created a dimension table for year)

The relevant model should look like this:

Nest create a slicer with the field from Area table, create visuals with the fields of both the fact tables and you should get this:

I've attached the smple PBIX file

[ziyabikram96]

https://community.powerbi.com/t5/Desktop/how-to-create-virtual-relation-with-DAX/td-p/424966
follow this i think it will help you

[mizaskun]

Thank you so much! I guess I am not getting it though. As I understand, TREATAS function is used to calculate a measure, right? I need just a way to filter the data. Is it possible? 

Thank you so much! 

[PaulDBrown]

TREATAS won't work beacuse both tables must contain the same field

[ziyabikram96]

use userrelationship function or a treat function

[mizaskun]

How could I use that? I am not calculating any measure. I just want to filter that info. Any suggestion? 

Thank you @ziyabikram96 !!!