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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

ambiguity problem, how to model the data to get it to work?

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

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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.Smiley Happy

 

Regards

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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?Smiley Happy

 

Regards

v-ljerr-msft
Employee
Employee

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.Smiley Happy

 

Regards

Anonymous
Not applicable

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

parry2k
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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