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

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

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

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
Super User
Super User

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/






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





JaredK Member
Member

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

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
v-ljerr-msft Super Contributor
Super Contributor

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

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

Anonymous
Not applicable

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.

v-ljerr-msft Super Contributor
Super Contributor

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

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 335 members 3,179 guests
Please welcome our newest community members: