Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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?
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 ;). |
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. | Proud to be a Super User! |
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 : 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 ;). |
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. | Proud to be a Super User! |
@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.
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 ;). |
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. | Proud to be a Super User! |
@ 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.
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 ;). |
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. | Proud to be a Super User! |
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.
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.
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 ;). |
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. | Proud to be a Super User! |
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 ;). |
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. | Proud to be a Super User! |
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.
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.
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 ;). |
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. | Proud to be a Super User! |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |