Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
np123
Regular Visitor

Filters have no effect on data

I've got a database that holds data modeled in the style of a star schema.

 

I'm reporting on this data in power bi desktop, and I have a fact table a date dimension and a table for "relative dates" that relates to the date dimention.

 

What I'm seeing is that when I try to filter a chart that uses date and fact data, using a value from the relative date table, the filter does nothing, as if it was not there. Is this an issue with they way I have modeled the data? I'm using a SQL Server data source with direct query mode.

 

 

6 REPLIES 6
MayssaSaade
New Member

I had the same issue while using filters from dimension table to act on fact table, 

to resolve the issue: Make sure that all your dimension tables are connected to the fact table using the same preliminary key. Ie. All dimension files connect through the same unique identifier in the fact table.

v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @np123,



I've got a database that holds data modeled in the style of a star schema.

A Star schema configuration is usually a central table(Fact table) with several Lookup tables(Dim tables). An example is a Sales actuals table with a lookup table for department.

 

So in your scenario, you should make the "Fact_Support_Reguests" table as the central table instead of "DimDates" table. Then you can set the cross filter direction to "Both" for the relationships, and the filter will work as expected.Smiley Happy

 

For more information about how to create and manage relationships in Power BI Desktop, please refer to this article.

 

Regards

It sounds like what I'm trying to do isn't possible. The cross filter direction is greyed out when I view the relationship. Having looked online, it appears that this happens because I'm using direct query mode.

 

I'm trying to create the abilitly to give users a slicer that contains options such as "Currenty Month" "Current Year". My initial thought was to create a view that brought these all together, so that each row in the date dimension can be identified as being in both cateogires.

 

I cannot relate my relative date table directly to the fact table as I would like to bring in a second fact table at some point, so I can have data from both fact tables in the same report, using the same date filter.

 

It's looking like I would need a cube in order to give this kind of functionality.

 

bullius
Helper V
Helper V

Looking at your pictures more closely, I may have got that wrong.

 

You might just want to filter by values in the Dim_Dates table. 

 

Basically, you want the relationship direction to point from the table with your filter values to the table you want affected by the filter.

 

Let me know how you get on!

Ah, it sounds like it's a problem with the way I have modeled the data, as the relative dates table can contain the same date twice (for example, once for "this week" once for "this month" etc.)

bullius
Helper V
Helper V

Hi @np123,

 

Try this...

 

The direction of your relationship between the Relative_Calendar table and the Dim_Dates table is the wrong way around.

 

Try deleting it, then dragging Date_id from the Relative_Calendar table to the Dim_Dates table.

 

You want the little black arrow on the joining line to point from the Relative_Calendar table to the Dim_Dates table.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.