Filter Question - How To Filter A Visual That Is Not Directly Connected To The Filter/Data

[danielpaduck]

Quick question and this might be easy for someone that has done it. Essentially, I have two data sets: Labor Detail and Budget.  

The Workbook that I took over has a Budget page with a Region filter.  I want to add the Labor Detail data to a matrix on the same page but want the Budget Region filter to also affect the Labor Detail Matrix.  Both datasets have a Region column that can be used. 

Thanks! 

[danielpaduck]

Yes, there are a ton of calculated columns in this data model.  I can try a UseRelationship. 

[danielpaduck]

I found the indirect relationship - 

However, not sure how to best solve this.  I do not want to break the data model since it is one of the main ones in production for financial data. 

[danextian · Accepted solution]

I understand that Offices will set the region but I am honestly not sure how filtering from Region table will affect LD Data. There's just too many tables in between. There's also too many calculated columns.

Your safest route now is too invoke the inactive but direct relationhip between Region and LG Data by using USERELATIONSHIP function in a measure.

[danielpaduck]

I did see that there was a Region table already. I was able to join the Budget table to Region but when I tried to join the Labor Detail table to region it could not make the relationship active indicating that there was another indirect relationship going to that table.  The weird thing is that I could not find that indirect relationship.  I have taken over the model from a developer that left and there is like 50 tables in this model. Is there anyway for me to find (easily) why it is throwing that error?  

[danextian]

The thing with indirect relationship is that they don't flow directly between two tables. There's usually another one or two in between. In your case, that's probably Region >> Table1 >> LaborDetails. In model view, there is an option to view all relationships under Model tab but it only shows the direct ones.

Still in the same view, I would just create a new layout then type region in the search bar and drag any table that contains region to the layout.

[danielpaduck]

I could try that but is there a way to be able to filter without an explicit relationship in the data model?  Thanks! 

[danextian]

You can use TREATAS or IN  in a measure to filter another table. Example:

CALCULATE (
    SUM ( budget[amount] ),
    FILTER (
        VALUES ( budget[region] ),
        budget[region] IN VALUES ( labordetails[region] )
    )
)

But why avoid a relationship? If you need another aggregattion from the budget table, you will have to create another similar measure. 

[danextian]

Hi @danielpaduck ,

Have you tried creating relationships? I would create a dimension table containing the regions and connect this to both Labor Detail and Budget. I would then use the region column from this dimension table in the visuals. The relationship would like below, flowing from the dimension to the fact tables:

And if you need to slice the data by date as well, you'll need to create a separate dimension table for that. You can use the CALENDAR function in DAX. You can create a region calculated table with dax (example: DISTINCT(LaborDetails[RegionColumn]) or you can use the Enter data feature under Home tab. You can create a calculated table under Modeling tab.