Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I come across this issue every time I try to do it. I eventually stumble across the answer but each time I start over and try again I get stuck.
The scenario is that I'm trying to create a Date table and establish relationships to other tables. Creating the Date table itself is easy.
DateAccounts = ADDCOLUMNS ( CALENDARAUTO(), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )
The hard part is in creating the relationship.
I've set the date columns in both tables to exactly the same thing:
I've created a many-to-one relationship from Accounts to DateAccounts.
Then I make a bar chart and set the Value to "Accounts.Count of ID" and the Axis to DateAccounts.Date but the chart is blank.
If I drill-down down to Quarter, I get a bar called (Blank). Obviously, a relationship has not been made.
This happens to me every time. Why don't the values align? What magic (obvious mistake more likely) am I missing?
If I keep messing with it I'll eventually change the magic bit and it will work. I just wish I could learn what the problem is so I don't have to hit this brick wall each time. This should be straightforward.
Solved! Go to Solution.
my recommendation will be to add DateAsInteger field in your data table as well and link with calendar table on DateAsINteger field. let's try this first and see what happens?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
First, see what happens if you change the date field in the axis from "hierarchy" to "date".
Also, try changing the relationship direction from single to both. It is possible that the relationship you have is flowing from the fact table to the dimension table, so that when you put dimDate as the axis, it is not passing the filter argument to the fact table.
Also, make sure that the data types are the same for both date fields.
my recommendation will be to add DateAsInteger field in your data table as well and link with calendar table on DateAsINteger field. let's try this first and see what happens?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Yes. This actually works!
There really seems to be some "magic" happening in the background that is making Power BI behave in a non-obvious way.
Good to hear 🙂
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
This happens when the data in your Accounts table can't find the corresponding record in your date table.
Is CalendarAuto working? What are the oldest and newest dates in your calendar table?
> This happens when the data in your Accounts table can't find the corresponding record in your date table.
I know. That's why I made this post.
> Is CalendarAuto working? What are the oldest and newest dates in your calendar table?
Yes.
Also what happens when you drag [Created On] to the axis of your visual?
> Also what happens when you drag [Created On] to the axis of your visual?
I get more wacky behavior from Power BI.
Hi @cparker4486,
Is there any chance you can share a cut down version of your PBIX file? I'd love to take a look and try and see why the visuals are mis-behaving for you.
Feel free to PM me a link to the file.
Cheers,
Phil
Hi Phil,
Thanks for the offer! That shouldn't be a problem. I'll message you some time this week.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |