Filters have no effect on data

[np123]

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.

 

[v-ljerr-msft]

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.

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

Regards

[np123]

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]

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!

[np123]

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]

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.