cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

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
Microsoft
Microsoft

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

JaredK
Kudo Collector
Kudo Collector

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

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net
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/






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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.