Power BI Date slicer not working as per data in the table

[rocku2008_1985]

I am trying  to create a dashboard. I used date slicer to filter dates to reflect data on the table. There are data in table till 2022 but the data slicer is moved beyond 4/31/2021 is not showing data in the tables. Could you please help me out/? I am stuck here. 

Well, when I move the date slicer it should worked as intended as it is showing blank data after April 2022 in the table even though I see there is data.

[KNP · Accepted solution]

It should be simple, only thing I can think of with the info provided is issue with the data model.

Can we get a screenshot of your data model so we can have a better understanding?

[rocku2008_1985]

@KNP : I hvae attached the necessary details as shown. I just want to link the date form the date table to all the tables in my model. SO that when I get the date slicer in my report and the filter the data it changes data or the chart of the tables created in the report section. Could you pleasehelp me out? I am stuck here.

[KNP]

Hi @rocku2008_1985,

As mentioned below, you should avoid many to many relationships.

Can you try changing the relationships to use the 'date' column from the date table in both joins and ensure you have marked the date table as a date table.

Let me know if you need this explained further. 

[rocku2008_1985]

@KNP : I changed the data type to date for both and then marked the date table as data table and I could only do many to many relationship or one to many relationships only. On doing them even the issue remains. I have attached all the screenshots.data table marked

[KNP]

Can you change the relationship on the 'Dispensed' table to Date[Date] >> Dispensed[Month].

IMPORTANT: Then change your slicers to use any date column from your Date table.

Everything should then work as expected.

[rocku2008_1985]

@ KNP: I changed hte relationship for both the tables, Referrals and dispensed to month and the date table to Date but still the same issue. I have also attached the updated data model.updated data model

dispensed table relationship change to month

[KNP]

Have you changed your slicers to use a column from the date table?

What behaviour are you seeing now?

[rocku2008_1985]

Hi I have the date column in the date table but for other table that I am linking has only month column
which is usually the monthend date.So the only option I have here is to link the month column from the referrals table
and lastdayofMOnth or Date column from the date table.

[KNP]

Hi, 

So a couple of things. 

You may need to ensure the date in your date table is formatted correctly. I see it has a time component. 

Also, have you marked the date table as a date table?

To check, right click on the top of the date table in the model view and choose 'Mark as date table' and make sure it is ticked. If it is not, click it and you'll be prompted to select your date column. Again, you should check the date column is formatted correctly before doing this step.

You should then create a relationship from Date[Date] to Referrals[Month] (it doesn't matter that you don't have an individual date column in the Referrals table.

Let me know how you get on.

[v-yueyunzh-msft]

Hi, @rocku2008_1985 

Thank you for your quick response and error detail providing. Baesd on the screenshot you provided.

You have some duplicate value in the 'Date' table and the key have duplicate value which cause the many-to-many relationships between your tables.And this will cause the invalid data intereaction situation as you provided.

To avoid this situation, you can try to use this dax to create a 'Date' table and use the [Date] column as the external key to connect to the other fact tables , instead of using  the [lastofMonth] .

Date= ADDCOLUMNS( 
CALENDAR(FIRSTDATE('your fact table'[Date]),LASTDATE('your fact table'[Date])),
"Year", YEAR ( [Date] ),
"Quater", ROUNDUP(MONTH([Date])/3,0),
"MOnth", MONTH([Date]),
"Week", weeknum([Date]),
"Year_Quater", year([date]) & "Q" & ROUNDUP(MONTH([Date])/3,0),
"Year_Month", year([Date]) * 100 + MONTH([Date]),
"Year_Weeknum", year([Date]) * 100 + weeknum([Date]),
"Weekday", WEEKDAY([Date],2) 
)

In the normal , you do not suggest you to create a "many-to-many" relationship between tables.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[KNP]

Hi @v-yueyunzh-msft and @v-rzhou-msft, 

I have unmarked this solution.

I am still having conversations with the OP. It is not appropriate to go and choose a solution for the OP when conversations are ongoing. It is confusing for the OP and misleading for others that may be trying to help.

Regards,

Kim

[rocku2008_1985]

Hi we have only month column in the referral table and dispensed table.  We do not have the other date columns. So not sure how I will do this. I have already the date table as well.datamodel of date and two other tables

[rocku2008_1985 · Accepted solution]

 I am attaching the screenshot of the datamodel as requested.  Also lastdayofMonth from date table is mapped to Month column of the referral table and dispensed table.

[KNP · Accepted solution]

It should be simple, only thing I can think of with the info provided is issue with the data model.

Can we get a screenshot of your data model so we can have a better understanding?