One Dimension Filters Another Dimension in Power BI

[Anonymous]

HI Team,

I am trying to find a solution to the below Power BI requirement.

Requirement:

The actual PBI data model(star schema) contains 7 Fact table, surrounded by Multiple dimension table (around 10-15).

I have added one more dimension table – Source Category which is also connected (created relationship) to all the 7 Fact table, so If I filter source Category all the connected 7 fact tables are filtering (as expected in star schema).

Now the requirement is If I filter the Source Category dim table all the other dimension tables also should filter the values. (ex : if I filter the source Category dim table as ‘Source 1’ then it should filter other dim table rows like dimcustomer as ‘Source 1’ ,dimproduct as ‘Source 1’ ..etc) Refer sample PBIX file attached

Please note: In the power BI report we are using all page filter instead of Slicer  

Methods tried:

1. I have tried to implement the solution provided by Radcad https://radacad.com/one-dimension-filters-another-dimension-in-power-bi but this will work only on slicers – not working on page level filter. (Actually, this solution is like hiding the other values based on measure in a slicer which is not working on page filter)
2. Enabling Bi-directional filter is not an option because it will create performance issues and issues with Dax measure with incorrect result.
3. Tried to create the relationship between source Category dim table and Dimcustomer table – it created an inactive relationship and throwing the below warning message .
4. Removed the relationship between source Category and 7 fact tables. Tried to create relationship between Source Category and other dim tables (DimCustomer and Dimproduct). Getting the below error.

Attached is the sample power BI file with screenshots.for sample only included 1 fact table and 3 dim table.

 https://drive.google.com/file/d/1LqWCmOFOo5259zK9F0r1RRih3lG3MCjG/view?usp=share_link 

  Please let me know how can we achieve this requirement

 @amitchandak @Greg_Deckler @tamerj1  @FreemanZ 

[Anonymous]

HI @tamerj1 

Thanks for your response. Yes I totally understand and agree with your point about Bidirectional Filtering. 

[johnt75]

You can't do it using relationships as it would cause ambiguity - multiple paths from the source category dimension to the fact tables through each of the other dimensions.

One option would be to create a calculation group with a calculation item like

Calc item =
VAR SourceCat =
    { SELECTEDVALUE ( 'Source Category'[Category] ) }
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        TREATAS ( SourceCat, 'Dim1'[Category] ),
        TREATAS ( SourceCat, 'Dim2'[Category] ),
        TREATAS ( SourceCat, 'Dim3'[Category] )
    )

where you would need to list all the dimensions you want to filter.

[Anonymous]

Hi @johnt75 

I have tried this calculation group approach

I put this calculation group in all page filter - selected the calculation group item,

selected "Source 2" in Source Category

but it is not working as expected. Still I am seeing all the values for product and customer table fields in the filter column. 

Thanks for trying to provide solution, much appreciated

[Anonymous]

I will explore this option and let you know my findings, Thanks for your quick response 🙂

[tamerj1]

Hi @Anonymous 
May I ask what is the reason for that? I mean all the fact tables will be already filtered. Do you want to filter the slicers from other tables/columns? or do you have other visuals based on dimtables?

[Anonymous]

Hi @tamerj1 , Yes you are correct the fact tables are filtered. 

Do you want to filter the slicers from other tables/columns? ->

1)Actually I wanted to filter the "page level filters(column values)" from other dim tables based on source category column.

2)In the report instead of slicers we are using all page filters, and we are using multiple dimension columns from different dim tables as filters. 

3)so the current expectation is if I filter the source category , other dim column filters also respond to source category filter selection

do you have other visuals based on dimtables? --> we have visuals based on the combination of columns from dim tables and measures based on fact tables. 

Hope this helps, Please let me know if you have any other questions.

[tamerj1 · Accepted solution]

@Anonymous 
The only way is enable bidirectional relationships between facts and dims. It works fine with your simplified sample file but probably won't work in the real example as the chances for ambiguities and circular dependencies are high.