Can't display the visual - despite simple model?

[dps]

Hello, Power BI "can't determine relationships between the fields".

I have three tables:

1. Unique budget IDs

2. Requisitions in progress with budget ID field (contains duplicates)

3. Purchase orders with budget ID field (contains duplicates)

I have two field relationships:

1. One to many from table 1 to 2 (single direction)

2. One to many from table 1 to 3 (single direction)

I am testing the relationships by creating a simple table with the same budget ID in three columns (one from each of the three tables).

• This works with the fields from table 1 and 2
• This works with the fields from table 1 and 3
• This does NOT work with the fields from all three tables
• This does NOT work with the fields from only tables 2 and 3

Power BI also does not let me create a relationship between tables 2 and 3.  There should be no link betwen individual requisitions and purchase orders, since the requisitions eventually turn into purchase orders (will drop off table 2 and appear in table 3 once approved).  However, the same budget ID may exist in both tables 2 and 3 at the same time, with both tying back to table 1.

Does anyone have any insight into what the issue could be?  Thank you.

[lbendlin]

Kudos for exploring the topic. Read about the preferred data model in Power BI (Star or snowflake).  Don't join fact tables directly unless you really really have to.

At the end of the day your data model needs to be able to answer your business question. That means it may have to change over time, and you may have to use different data models for different questions.

In your scenario Table 1 is a dimension table that controls the two independent fact tables 2 and 3.  That also means that 2 and 3 don't know about each other (that's how it should be).  This leads to the effects you are observing.

[dps]

Hello, thanks for your reply.  This is all very new and interesting to me, but with fresh eyes, I do not see how to solve this problem that seems simple as an outsider...

So if Power BI can't handle showing the data from tables 2 and table 3 (even though they both relate to the main table 1), and I can't link between tables 2 and 3...

...then it is a dead end?  It sounds like I'd have to separately merge each of tables 2 and 3 to the budget ID rows in table 1 and get the aggregate values... but then you can't work with any line item detail.

[lbendlin]

it's not a dead end.  Add a column from table1 to the visual.

You may also want to explore other BI tools.  Qlik Sense in particular is based on an associative model which seems to better fit with your way of thinking.

[dps]

Thank you - I will check it out!

So it was not working before because I was not using summarization.  I had loaded up the same Budget ID from all three tables into a regular Power BI table with three columns.  But they loaded as "don't summarize".  Once I switched to "first" (text ID field), now I can see the three codes line up per row on the table as expected, since they're one-to-many.

I knew it didn't make sense that PBI couldn't handle this...