Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply

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

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. screenshot_1.PNGscreenshot_2.pngscreenshot_3.PNG

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.

2 ACCEPTED SOLUTIONS
KNP
Super User
Super User

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?

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

 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.datamodel_powerBi.PNG

 

View solution in original post

13 REPLIES 13

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

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. 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

@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.dateformat_datetable.PNGdata table markeddata table markedrelationship_date_referrals.PNG

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

@ 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 modelupdated data model

 

 

dispensed table relationship change to monthdispensed table relationship change to month

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

What behaviour are you seeing now?

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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.powerBI_date_realtion_Pic.PNG

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.

 

KNP_0-1669051277174.png

 

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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

 

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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 tablesdatamodel of date and two other tables

 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.datamodel_powerBi.PNG

 

KNP
Super User
Super User

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?

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.