Date Slicer partially working on multiple tables

[Anonymous]

Hey everyone,

I'm building a dashboard from an Access database I built for our quality team which has multiple tables and queries.  I made a date table to act as the 1 to many link between all the tables (they have time-stamped entries formatted as date/time) and it works!  At least on qryScore... not for qryObjByDept.  On this one, if I move the beginning date to 1/2/2022 or any date beyond, all the data disappears which sounds like the table sees all the objections as entered on 1/1/22 which is not the case.  

The other one, qryScore seems to work fine.  I'm running into a wall on this one and can't help but think I'm missing some small setting.  What am I doing wrong?  I'll try to attach the files here... I started a new dashboard with small visuals just to illustrate the problem; my actual dashboard is much larger, but the same problem exists among all the other queries excetp for qryScore.

Thanks,

Jordan

[Anonymous]

Hey @selimovd I checked the connections and they appear to be correct:

Also, here is the dropbox link:

https://www.dropbox.com/s/kx3eb5dzqytec34/Date%20slicer%20example.pbix?dl=0

Thanks,

Jordan

[selimovd · Accepted solution]

Hey @Anonymous ,

in general I would not use a column from the format date/time to do the relationship between a date table.

Basically there has to be a match between two exact values, so "1/5/2022 3:34:27 PM" is a different value than "1/5/2022 3:34:28 PM" and "1/5/2022 3:34:26 PM". So they all would not match with "1/5/2022 12:00:00 AM".

As far as I remember still Power BI accepts that, but I just would not do it as it's not good practice. Just change all of them to the date format or add another column with the date only.

But this is not your problem in this case. Your problem is that there is something wrong with the date column in the table 'qryObjByDept' and it's really hard to recognize. Although I changed the format to Date, I realized in the VertipaqAnalyzer, that there are some violations in the relationship of this table:

I then added another date column with a calculated column:

Date_New = DATE ( YEAR ( qryObjByDept[DateAdded] ), MONTH ( qryObjByDept[DateAdded] ), DAY ( qryObjByDept[DateAdded] ) )

After using this column for the relationship with the date table it eventually works:

So either add a calculated column with the date format or do that already in Power Query and add a proper date column there. Then it should work with the relationship. You could also try to change your date/time column to date already in Power Query, but I'm not sure if that works.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd That worked perfectly!  Thank you!

[selimovd]

Hey @Anonymous ,

can you check if you connected the correct fields? Just do a double click on the connection itself.

Otherwise it would be useful if you can upload the file somewhere (Dropbox, OneDrive, etc.) and share it with us.

Best regards

Denis

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic