Date filter applying to one table but not another

[DanGiles]

Hello, 

I have a number of tables in my dashboard related to project spend and budget. I am trying to add a date filter onto the dashboard so that I can look at project spend during defined periods. There are two tables in particular that I would like to filter using a common slicer tool. Table A contains time bookings to the project (timesheet bookings) and Table B contains project expenses. Both table A and B have a column related to date, and as far as I can tell they are formatted in exactly the same way (Data type: Date, Format: Short Date). Following some advice from another forum post I have produced a Calendar Date table which contains every individual date from the start of the project until the end of the project which also has the same data type and format. Currently I have created a relationship between the date from the Calendar Date table to Table A and Table B (One-to-many, filtering both ways).

I have tried adding a filter to the dashboard based on the Calendar Date table, and it seems to filter the data from Table A, but not Table B. Because of this, applying the date filter also seems to remove rows from my dashboard table where the value for Table B is 0. 

The aim is to have a slicer that filters the total cost from Table A and Table B at the same time.

The below screenshot shows what my dashboard currently does. When I create the filter, row 9 gets removed because there is no value in the 'Spend to date' column (from Table A). When you change the dates in the filter, the 'Spend to date' (from Table A) column value changes but the 'Expenses to date' (from Table B) does not. Row 8 disappears in the final screenshot because there is no value for 'Spend to date' in that period which is fine, although I would prefer it to stay in the table but with 0 values.

[HotChilli · Accepted solution]

OK, so the problem is - if the relationship was made active, there would be 2 paths to get from Dates to Expenses and powerbi does not allow that.  The original problem you posted is because Dates is trying to reach Expenses along a long path of relationships via Bookings.  When it gets there (or gives up) the data in Expenses doesn't match up.

--

The conclusion is that the model is incorrect and/or the relationships that have been created are incorrect (many-to-many, filtering both ways).

Try and rethink the model and see what table needs to be filtering which others and get the filtering direction correct.

It will then allow you to make the Dates/Expenses relationship active.

[HotChilli]

There is an inactive relationship between Expenses and Dates.  That's problem number 1.

"but I get an error related to relationships" - What was the error?

[DanGiles]

@HotChilli - When I attempt to make a visual using the two fields in the dashboard I get an error in place of the visualisation saying "Can't display the visual. See details", and when I click on see details it then says "Can't determine relationships between the fields. Can't display the data because Power BI can't determine the relationship between two or more fields".

I have attempted to make the relationship active between Dates [Date] and Expenses [G/L Date] and get the following error:

[HotChilli]

OK, we're getting somewhere.

Put the date from the date table and the date from Table B in a table visual.  Just those 2 fields, no external slicer.  Does the visual show dates matching up?

--

While you do that, can you post a screenshot of the relationship dialog (edit the Date table->TableB line, click Properties)

[DanGiles]

@HotChilli

I have tried putting the two date fields into a table (from Date Table & Table B) but I get an error related to relationships. I then tried the same but for the Date Table and Table A and that did work.

The screenshots below show the relationships between each of the tables in the dashboard, as well as the properties of the specific relationship between 'Table B' (EXPENSES) and the Date Table (DATES)

[HotChilli]

If you have doubts about the relationships working correctly, test them in a separate report page.  Create a slicer from the date table and put two table visuals with only fields from each of the tables.

Example,
TableA visual : date and an aggregate, both from Table A.
TableB visual : date and an aggregate, both from Table B

See if that works.

---

If your relationships are working OK, it will be the combination of fields in the original visual or a relationship problem.  Would need more info on both.

--

Also, it's usually best to have single direction relationships unless you have a good reason

[DanGiles]

@HotChilli thanks for your response. 

I have tried creating the two separate Table A / Table B visuals in a separate report page, but I still have the same issue where Table A is filtered by the date filter, but Table B is not.