Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table with POS, and another with Shipments.
I concated the Year and Period on each of these tables into a new column for each, called "Year.Period"
I did the same on my Date table called it "Year.Period"
When I go to connect a relationship from each fact table to the date table, it says I can only have 1 active relationship..
I want to be able to look at both Fact table metrics using the date table for the dates on the same visual.
I'm stuck and would love some help.
Solved! Go to Solution.
@Anonymous in PQ add a new column with the following expression:
make sure column name are exact, since PQ is case sensitive
#date([Time.Year], [Time.Period], 1 )
using DAX to add new column, use this DAX expression for a new column
My Date Column = DATE ( YourTime[Time.Year], YourTime[Time.Period], 1 )
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
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.
@Anonymous seems like a year and period column data type is set to text before you add this new column, can you change the data type of these columns to number because that is what is required to add the new date column.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
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.
Thanks again @parry2k. I completed the adding of "date" column to Shipments.
I then connected "shipment table" to "date table", and "POS Table" to "date table".
I'm still getting the same error though. I am unable to make both relationships active... Any ideas? Thanks again!
Any help is appreciated!!
@Anonymous in PQ add a new column with the following expression:
make sure column name are exact, since PQ is case sensitive
#date([Time.Year], [Time.Period], 1 )
using DAX to add new column, use this DAX expression for a new column
My Date Column = DATE ( YourTime[Time.Year], YourTime[Time.Period], 1 )
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
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.
Thanks @parry2k ! I tried to add the column you were nice enough to help me out with to the Shipments table.
However, I get this error...
@Anonymous you can add dummy date using the period column, maybe first of each period month and that will become your date and then you can set the relationship in the date column.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
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.
Thanks! yes that could definetly work. So I know how to create a column. How would I exactly make it a dummy column I can have a date in? Would you be able to help with the formula please?
These are the only date columns I have in Shipments table....
@Anonymous why you are having many to many with the date table, you should be able to connect on date column instead of year.period column
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
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.
@parry2k Thanks for responding!
My POS table has a "week end date". I created a one to many relationship now, from the Date tabe (date field) to the POS Table (Week end date).
The issue is my Shipments table only goes down to Year and Period (period as in month number). So I don't have a date I can create a relationship to "date" in the Date table with. That's why I created the year.period column on all three tables, and ran into the issue I am running into now.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |