Supporting Date Table not working

[Ndz1985]

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.

[BrianJ]

@Ndz1985,

Welcome to the forum - great to have you here!

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:

1. to filter the fact table via a virtual relationship with the date table using TREATAS.

https://info.enterprisedna.co/dax-function-guide/treatas/

2. 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.

Problem of the Week #4 (PQ) - Addressing Granularity Issues

From @haroonali1000: OK, here we go! Problem of the Week #4 is now live! For background on this initiative, check out this post and this week’s launch video that Brian and I did on YouTube. Problem of the week #4 First Challenges and now problem of the week!! Don’t worry it’s just a quick guest appearance. So this week’s problem of the week was something I was tackling back in November and while chatting it through with the Oracle (@BrianJ) he said it would make for a great problem of the…

I hope this is helpful in addressing your question.

• Brian

[Ndz1985]

Hi Brian,

Thanks for the speedy reply. I used the TREATAS function as described but still found no luck.

I’ve uploaded the .pbix if anyone has time to review.

ABS Industry Analysis.pbix (37.1 MB)

[AntrikshSharma]

@Ndz1985 You were using the TREATAS incorrectly:

[Ndz1985]

Thanks my man!