Solved: Re: Dates table relationship not working properly

Tomfiki · ‎11-18-2020

Hello all,

Could you please help me solve this issue with which I've been losing my mind for hours?
I have a 'Data table' with "Invoice date" with a relationship to 'Dates table' which I created through CALENDARAUTO(6).

For all calculations in 'Data table', measures and other visuals, the connection works OK. But when I created a 'Table' visual and apply slicers from 'Dates table' it is not working properly. Instead of filtering out the rows (so they would disappear), only the number columns (like Sales and Cost) become blank but the rows still stay in the table.

I tried all recommendations I found on the forum -
1. changing data type for both columns to Date (or also to Date/time),
2. changing data type of the 'Invoice date' to both "type date" and "type datetime" in Power Query Editor,
3. creating a separate column in both tables in date format ("dd/mm/yy") or in text format and creating a relationship between these new columns.

4. changing the relationship to Single and Both.

And nothing works. When I insert the linked columns ("INV Date" from both tables) into the table (which I presume should have the same value), the value is different.

I'd be greatful for any help. Thank you.

Tomas

Tomfiki · ‎11-18-2020

Nevermind. I finally figured it out. There was a 'Margin' measure inserted into the table

= 1 - SUM([Cost]) / SUM([Sales])

which messed it all up. Such a stupid mistake.
Thank you anyway.

View solution in original post

daxer-almighty · ‎11-21-2020

Hi @Tomfiki

Bi-dir filters can slow down the model substantially, so it's not only about model ambiguity. Bi-dir filters should be used in a very precise setting: when you have a many-to-many relationship with a bridge table in between the involved tables. But this many-to-many has to be of the sub-type "one-many-many-one," not "many-one-one-many," the latter technically being a granularity issue where you should use the many-to-many relationship (that is, *:*). This is the one legitimate reason to use them.

As for the second bit... I told you what is the best way to handle very detailed reports and this method will always be paginated reports since these can gracefully handle the layout down to the very last pixel. Visuals in PBI do not offer this level of customization.

daxer-almighty · ‎11-18-2020

Hi @Tomfiki

I've had a look at your file... Why do you use bi-dir filtering on dates? I can't see a reason why this should be so. What's more - IT SHOULD NOT BE SO. Please stay away from bi-dir filtering as much as possible if you want to stay sane and want to understand what your model produces (look for one of the latest vids from Alberto Ferrari on YT about why you sholud not use bi-dir filtering). There are very precise rules WHEN bi-dir filtering should be used; your model does not guarantee them. That's the first observation. The second is this: such reports should be created as paginated reports, not as regular PBI reports. PBI is meant to summarize data and it's really good at it, not to display details the way you're trying to do. To do what you want you should revert to the Report Builder for paginated reports.

Tomfiki · ‎11-18-2020

Hi @daxer-almighty,

Thank you for your advise.

I've now watched the YT video you mentioned and removed the bi-dir relationship from dates tables.

Can I ask what are the exact rules? I understand I should never use bi-dir relationship when it creates 2 (or more) pathways from one table to another. But if it is only between 2 tables while one of them has no relationship with any other table... why it then creates an issue?

Regarding your second observation - I just need this one page to list all the details available. Apart from that I have multiple other pages to summarize and display data while dynamically selecting measures etc. - to the best of my very limited knowledge. I just deleted these pages since it had nothing to do with this perticular issue.