One-to-many relationships between two tables - Find totals

[aashton]

I have a table Leads, with ID, Email,Name.  This links one-to-many on Lead Type, which is the Type Id and Type Name.  Leads also links to Applications one-to-many with Application data.  I'd like to display Applications by Lead Type.  For example:

Email, Lead Type, Application ID

And even count total applications by Lead Type.  When I put these fields together in a table, it tells me it can't identify a relationship.  This works in SQL, can't figure out why it won't work in Power BI?

[Cbutler]

@aashton can you share your PBIX file? 
or screenshots/examples of the data and relationsip set up? 

[aashton]

@Cbutler I put together a very basic sample pbix.  I'd like to display a lead, with their applications and contracts together in one table.  I am getting an error saying unknown relationship.  I also need to count applications and contracts by lead type, and am having trouble with this.  

https://drive.google.com/file/d/1F_Y9zMTOunkCU06T5iterJZct2y7POOX/view?usp=sharing

My original table structure was Lead Types to Applications many-to-many and Applications to Contracts many-to-many.  So I created the intermediary tables to create one-to-many relationships, thinking I was doing the right thing, but now i can't seem to get anything to work. 

[gmsamborn]

Hi @aashton 

I took a look at your model and it makes much more sense than it would have with a many-to-many.

If you change you relationship between Leads and Lead Types from Cross Filter Direction to Both.

Then a simple measure like App_Cnt = COUNTROWS( 'Applications' ) should work for you if I understand your requirements correctly.

[aashton]

@gmsamborn Thanks, I am trying to see Total Applications by Lead type.  And you see in my table example, it just shows the overall total for each type, instead of the total for only that specific type.

And I can't put lead, lead type, application data together in one table.

[gmsamborn]

@aashton 

Take a look at my example.  I only changed that 1 relationship to "Both".

Leads Sample - mine.pbix

[aashton]

@gmsamborn And, do you know how I can display it all together in a table?  Lead name, type, application date, contract date??

[gmsamborn]

Hi @aashton 

I'm not sure why yours would react differently if there are no other slicers/filters in use.

As far as not being to put them altogether in one table...  If you look at the relationships in your model, there is no to get from Applications "uphill" to Candidates.

You could make that relationship BOTH as well but I'd be careful about possible unintended consequences.  (I try to avoid relationships with filtering set to BOTH as well as many-to-many relationships whenever possible.)

[aashton]

@gmsamborn Are you able to put them all together in a table in your example?  I think the only way to do it is change it back to many-to-many.  But that screws other things up.  The Leads, Applications and Contracts table all have dates and states I need to filter on, and I couldn't connect them all to the Date and States table with many-to-many.

[gmsamborn]

Hi @aashton 

Instead of going back to a many-to-many relationship, I would consider changing the relationship to BOTH.

You might have to check for possible ambiguity in your model because of this change.  (Check for multiple paths between 2 tables that were made possible by the change.  An example of this in your Model View posted above would be how you can get from  'dim_Date' to 'Application Raw Data' either by the direct relationship OR via 'dim_Date' --> 'Lead Types' --> 'Leads' --> 'Application Raw Data'.)

One possible way to get around possible ambiguity in this case might be to make that relationship inactive as well.  This might not be a feasible option since anywhere that a reference to 'Candidates' is made, USERELATIONSHIP() would have to be used.

Without seeing your model, I'm just guessing.

[aashton]

@gmsamborn Oh yes, I see.  My actual file does have both as the cross-filter between Leads and Lead Types.  But it's much more complex, there must be something wrong somewhere else: