Forum Discussion
Helper IIFiltering two unconnected tables
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!!
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
8 Replies
ziyabikram96Helper V
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 !!!
- 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!
PaulDBrownCommunity Champion
TREATAS won't work beacuse both tables must contain the same field
- 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
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