Forum Discussion
Values Not Showing from Date Table
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?
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 ) * 7If 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 ) * 7Now you can create a relationship with the datetable using the data.
4 Replies
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)
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?
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 ) * 7If 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 ) * 7Now you can create a relationship with the datetable using the data.
