Reply
Frequent Visitor
Posts: 5
Registered: ‎08-22-2016
Accepted Solution

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


Accepted Solutions
Super Contributor
Posts: 1,366
Registered: ‎07-17-2016

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

Hi @pcand01,

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


All Replies
New Contributor
Posts: 630
Registered: ‎07-22-2015

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

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/

Member
Posts: 68
Registered: ‎12-20-2016

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

Hi @pcand01, 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
Super Contributor
Posts: 1,366
Registered: ‎07-17-2016

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

Hi @pcand01,

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

Frequent Visitor
Posts: 5
Registered: ‎08-22-2016

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

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.

Super Contributor
Posts: 1,366
Registered: ‎07-17-2016

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

Hi @pcand01,

 

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