Values Not Showing from Date Table

[cyncyn_98]

Hello everyone, 

I have an issue that I can't seem to solve after reading so many articles. I have three tables that will correlate to the Date Table on 'WeekID'. I have created a matrix table that have columns containing values from each of the three tables. However when I use the slicer that filters on a date, it only works on the columns that are contained in the Weekly Onsite table.  

Below is the modeling of the tables to the Date table on 'WeekID'.

Below is the relationship that only seems to work between 'Date Table' and 'Weekly Onsite' on the WeekID column. The other tables mirror the same relationship to the Date Table as well.

 Below is a snipshot of the matrix table I'm trying to create, only the columns in blue show the correct data. The rest is either wrong or missing data. 

I have formatted the date columns in Power Query and the WeekID values match with those of the working tables. If more information is needed, please let me know since I have hit a wall. Is there a solution that can spit out the data correctly? 

[cyncyn_98]

@PC2790 Thank you! That worked perfectly

[PC2790]

Hey @cyncyn_98 ,

Are you picking the Date for slicer form the DateTable?

While looking at your datamodel, the only thing strikes is the many to many relationship which sometimes produces ambigous results.

Do you want to consider having one to mane relationship with Date Table using Date column?

See if this article turns out to be of any help

And if you really want to use WeekID as the common field, you can try creating a bridge table to have one to many relationship

How To Deal With Many-To-Many Relationships In Power BI (burningsuit.co.uk)

[cyncyn_98]

Hi @PC2790 ,

I'm picking the date slicer from the Date Table with no issues for one of the tables.  I have read the articles and followed the tips to get the values to show, but no luck. 

Below, I have created bridge tables to create the 'one to many' relationship instead of 'many to many' relationship for two tables. And only the Onsite table values still filter on the slicer instead of the other two tables.

And only the Onsite table values still filter on the slicer instead of the other two tables. The columns circled in blue show the the values correctly filtered. 

Are there any other options I can do?

[PC2790 · Accepted solution]

Let's try it other way.

Do you have dat columns in your other weekly tables?

If not, if you have week, we can know firstdate of the week and generate a date column using

Date = 
var _year = 2022
return
DATE(_year , 1 , 1) + ('Table'[weeknum] - 1 ) * 7

If the first week starts from January 3, 2021, then modify the formula to

Date = 
var _year = 2022
return
DATE(_year , 1 , 3) + ('Table'[weeknum] - 1 ) * 7

 Now you can create a relationship with the datetable using the data.