Hi, I’m sure I’m over looking something really simple here.
I’ve created a supporting date table to connect a fact table and the date table in order to reconcile the relationship with one to many. When I try to filter my data using my original date table it’s not filtering.
I’ve trimmed and cleaned all columns.
Any help appreciated.
It’s always the case that if you can post your PBIX file, you’ll get much better support on the forum - faster response and more specific solutions.
In this case, it’s hard to tell exactly what’s going on without seeing your data model, but for me at least, the two preferred ways of dealing with this sort of granularity mismatch are:
to filter the fact table via a virtual relationship with the date table using TREATAS.
to allocate the data in your fact table from the yearly down to the daily level in Power Query, and then connect the Date and Fact tables with a 1:M relationship, since now the granularities match at the daily level. Check out Problem of the Week #4 - Enterprise DNA Expert @haroonali1000 based an entire two week problem-solution cycle around this issue.
I hope this is helpful in addressing your question.