How to set filters to show only items linked to fact table and also work in cascade mode?

[flamcnet]

Hi, 

I have a filter (I cannot use slicer) on the Filter pane in the "Filters on all pages" session that is pointing a dimension table and also a table visual with some columns comming from the fact table. The issue is that the filter is displaying all items from the dimension table. I want to display only the items that have values related to the fact table. Also, if I click in an item from the the first filter, the second filter is not being filtered by the first filter. I need the cascading behaviour also working.

I am developing a Power BI report by using Power BI desktop and Direct Query pointing directly to Postgres views. I have manually created the relatioships where 1 is on the dimension side and * is on the fact side. Cross-filter direction is set as Single and "Assume referential integrity"is set as Yes.

Is there a way to configure the filters (10+) to work as I expect (in bold)? 

Thanks,

Flavia

[v-agajavelly]

Hi @flamcnet ,

As you said that where filters should only show values that have data, and should cascade properly across multiple fields.

You're absolutely right, the filter pane in Power BI is not built to support that logic it will always show all values from the dimension, regardless of what’s in the fact table. And unfortunately, even with Both cross-filter direction or an IsInFact helper column, the filter pane still won’t dynamically limit the values unless you switch to slicers.

What we’ve found to work in real-world solutions. Move from filter pane → to slicers on report pages, even if they're hidden, styled, or synced across pages.
Why slicers work.

• You can control them with "Show items with no data" = Off
• They cascade/filter each other naturally via relationships
• They only show values from the dimension that exist in fact
• You can use sync slicers to apply them globally (like filters on all pages)

You can group them visually or even place them off-canvas if you want them to behave like backend filters but still get the right filtering logic


Regards,
Akhil.

[v-agajavelly]

Hi @flamcnet ,

Just wanted to check in and see how things are going with your model were you able to try out the suggestions from @hnguy71 and the tips around cross-filter direction and filtering with a helper column. Sometimes those small tweaks like setting the relationship to Both or using a flag column like IsInFact can make all the difference when filters aren’t behaving as expected. If it’s all working now awesome. But if you’re still seeing anything odd or unexpected, feel free to share more details always happy to dig in and help out further.

Regards,
Akhil.

[flamcnet]

Hi @v-agajavelly , 

Sorry about my delay. I was on a medical leave.

No progress yet. That "Both" option was also tested but it is also not working as expected. That IsInFact option seems to work with only one small use case like one fact and only one dimension. 

I have opened a ticket with Microsoft and they said Filters won't work as I need. This is by design. I will proceed on DCR process with them and wait for some approval/implementation of this "enhancement".

As a workaround I brought the dimension data to the fact table and I am pointing the filters to the fact, instead of the dimensions. But I believe this is a really ugly solution in terms of performance and design.

So, you never had to apply my use case to any client? (Point filters to dimensions and have in the filter items only the values that have data related to the fact tables to avoid the users to select filters that brings no data?

Thanks,

Flavia

[v-agajavelly]

Hi @flamcnet ,

Thanks for the great question and for sharing your model.

@hnguy71  suggestion is a useful debugging technique testing with a basic measure helps verify if filter propagation is working through your model. It's a great first step to diagnose any issues with your relationships.

Try this to work as you required.

• Ensure cross-filter direction is set to BOTH for cascading filters to work.
• Use a DAX helper column (IsInFact) to filter out unrelated dim values from the filter pane if needed.

Hope that helps and many thanks to both Super Users for their valuable suggestions. Please try the suggestions above and let us know if it resolves your issue.

Regards,
Akhil.

[HarishKM]

@flamcnet  Hey,
I will suggest 
You can bring your required filter column from your dimension to fact table using left join and this way you will matching record from your other table.

you can use below code

select

a.column1

a.column2

....

b.column1

b.column2

* from table 1 a

join table 2 b

on a.id = b.id

Thanks

Harish M

Please accepts this as a solution if it is solve your problem and give kudos as well

[flamcnet]

Hi @HarishKM, 

I understood your solution but something I didn't understand is: what is the reason of having dimensions and fact tables in the model if I bring all values to the fact table in a query?

Thanks,

Flavia

[hnguy71]

Hi @flamcnet 

The filters should, by default, return only the information that has filters applied to them. Could it be possible that your relationship and/or aggregate fields are not taking those into consideration? Can you share a screenshot of your model (with relationships?)

[hnguy71]

Hi @flamcnet 

I now believe I am understanding your issue. What you're saying is when you select the dimension column from the filter pane, it shows all available values regardless if the fact table contains any data for that value-pair.

Because a dimension, by design, is meant to show all available attributes to help compliment the fact table. Your relationship direction is dimension --> fact, therefore, you will not be able to filter out values that does not contain any data.

What @v-agajavelly suggested is very valid, and if you can, create a custom column on your dimension table(s) to check if there are any values in the fact table. For example, if we're going to call this new column as "IsInFact" with an output of "Yes" or "No", once you've created the right evaluation, add that field to your filter pane, and filter it for "Yes". Hide and lock that filter and your dimension should propagate and only show values has some data.

[flamcnet]

Hi @hnguy71 - Yeap, now you fully got it. 🙂

I tried to implement the solution proposed by @v-agajavelly and it worked well for 1 Dim table. I went ahead and applied the solution to the other 6 Dim tables. I could see the filters listing the correct values but the report page itself "died" loading data for a simple table visual with two columns and less than 1000 rows. It was probably due to all the other 6 merges I had to do in order to create that "IsInFact" Yes/No column.

Another solution that "worked" was to bring all dim values to the fact view and use it in the filters but I really believe this is not a good solution.

I keep trying to find alternatives... 😞

[hnguy71]

Hi @flamcnet ,

Your model looks okay.  Do you have a measure to test the values coming from your fact table? Any basic measure will do.

For example, 

Base.Total = SUM(FACT_TABLE_IN_QUESTION[Amount])

And then, to debug, using that measure in a table visual along with one field coming from the dimension table that's showing you all the data. You should only see aggregated sums of those fields in scope. 

Next, once you've confirmed that it's good, add the filter that you're planning on using into the same visual and check to see if only the required information is being shown. 

[flamcnet]

Hi @hnguy71, 

Yes, I do.

It is a simple count: Controls_Count = DISTINCTCOUNT(Fact[Control]), I have added it as card.

I have created a table visual, with one column(control) comming from the fact table and another column(tower) coming from the dimension related.

Both tests on "filters on this visual" and "filters on all pages", for the cases where the item in the filter with dimension items does not have a respective in the fact it returns blank. My goal is to prevent the user to click in a filter item that does not bring any data.

It seems that the filters in the filters pane is going to bring all values from the dimension table, no matter what.  😞

Do you see any other alternative that is not to go for using slicers?

[flamcnet]

Hi, thanks for answering. 

How can I check if "relationship and/or aggregate fields are not taking those into consideration"? I already have constraits in the DIM tables to make sure they don't have duplicated values. I believe the relationship is in good shape because this is reflecting exacly what you see below. I ran also some queries to make sure if I don't have any data integrity isses and it seems all good.

Here is an screenshot of my model:

Just to explain I have one main fact in the center and other two other facts with diferent types of granularity level.