Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a data model with the following
- A Client table with EffectiveDate and clientNumber
- An invoice table with BilledDate and ClientNumber
- A calendar table with CalendarDate
I need to filter the report on the year. My problem is that I canot join all the tables.
I can join Invoices to calendar on BillDate and Invoices to Client on ClientNumber but I cannot join Client to calendar on EffectiveDate, I cannot make this relationship active, it would iintroduce ambiguity.
What I need to do is filter on Calendar, i.e. I select a year and it will select both invoices for that year and clients effective this year. Furthermore, I want to be able to filter on the cllient as well, i.e select 1 client category. I would get the client records for that category where they are aerfective this year and I would get the invoices for these clients and that year.
I guess I am missing an obvious solution. I am just starting with PowerBI.
Thanks
Solved! Go to Solution.
Hi @Anonymous,
I can join Invoices to calendar on BillDate and Invoices to Client on ClientNumber but I cannot join Client to calendar on EffectiveDate, I cannot make this relationship active, it would iintroduce ambiguity.
In this scenario, you can just create an inactive relationship to join Client to calendar on EffectiveDate first, then make use of USERELATIONSHIP Function (DAX) to create a new measure to get your expected result.
In addition, here is a similar thread for your reference.
Regards
Thanks for all your replies. I ended creating a view for invoices where I sum the invoices and have a composite key, Customer Number plus Invoice Year. I added a similar key on the customer side, Customer Number plus Effective year and I joined on that key.
I note the use of DAX to use an inactive relationship, this may also come very handy. I tried creating a couple dim fact tables with no success Getting errors Link direction isn't valid for this relationship. This last one has a workaround, hit the enter key before the error message pop-up but still, creating the composite key was the easiest solution for me. Just enough to propagate the filtering as expected.
Hi @Anonymous,
Great to hear the problem got resolved! Could you accept helpful replies as solution to help others who may also have the similar issue easily find the answer and close this thread?
Regards
Hi @Anonymous,
I can join Invoices to calendar on BillDate and Invoices to Client on ClientNumber but I cannot join Client to calendar on EffectiveDate, I cannot make this relationship active, it would iintroduce ambiguity.
In this scenario, you can just create an inactive relationship to join Client to calendar on EffectiveDate first, then make use of USERELATIONSHIP Function (DAX) to create a new measure to get your expected result.
In addition, here is a similar thread for your reference.
Regards
Hi @Anonymous, you will need 2 client tables, a ClientFact and ClientDim table.
ClientFact would contain the EffectiveDate, ClientNumber, and any data fields.
ClientDim would contain ClientNumber as well as any client attributes, such as name, location, etc.
ClientDim connects to ClientFact on ClientNumber
ClientDim connects to Invoice on ClientNumber
Calendar connects to ClientFact on EffectiveDate
Calendar connects to Invoice on BilledDate
Here are some links for your reference, take a look and see if can reach to any solution, if need further help, please ask.
https://community.powerbi.com/t5/Desktop/Date-Table-Relationship-to-Multiple-Fact-Tables/td-p/65816
http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |