Log In
Register
Home
Power Query
Power Pivot
Power BI
August 6, 2016
Offline
Hi Everyone,
I think I'm misunderstanding something basic about relationships and it's keeping me from progressing. I'll give you a simple example and how it's not working.
I have a DIM table with client names and an original contract amount. For example, client A is having us build a building for $100.
My FACT table is a table of invoices. We invoiced client A $60 on Oct 1, $10 on Nov 1, and $15 on Dec 1.
I upload these to Power Pivot. I create a relationship between the client names.
I build my pivot table starting with client from my DIM table, the contract amount from my DIM table, then I add revenue by month from my Fact table. I even add a measure showing how much is left to invoice. But if I add my date field from my fact table - for example, if I want to see what the amount left to be billed was in October - it breaks. It says I need a relationship. I HAVE one. What is it about relationships that I'm not seeing here that is causing the date field to break this thing?
I spent about 4 hours yesterday and 2 already today trying to get this simple pivot table to work and I'm just at a loss.
-Nate
Power Query
Power Pivot
Power BI
August 6, 2016
Offline
I should mention, I've tried adding a date table and linking it to the Fact table, but that doesn't really do anything.
I've also tried adding a start date to the DIM table but I can't relate that to the Fact table because there are duplicates in both tables.
I tried relating the date in the DIM table to the date table but it creates a circular reference.
Moderators
January 31, 2022
Offline
As far as I could determine, adding the date field the did exactly what it was supposed to do. That is, return the total contract amount, the revenue for that month and the difference between the two.
However, you want to see the revenue up to that month and then the remaining amount on contract. I created a Calendar Table and a measure that calculates the YTD revenue, based on the month selected from the Date hierarchy.
Now the 'missing relationship' issue may be ignored. The pivot table works as you want it to. It comes up as soon as you put a field from a Dim table in the Value area AND a field from the Calendar in the filter, row or column area of the pivot table. But in this simple model it doesn't matter.
In a more complicated model you would probably have a Calendar table (dim), a Client table (dim), a Contracts by Client table (fact) and an Invoices by Contract table (fact). Then you would need a different set of relationships and measures to produce the kind of report you need.
Power Query
Power Pivot
Power BI
August 6, 2016
Offline
Thanks, Riny. I think that's getting me closer. I'll keep playing around with it and see if I can define my problem better.
I'll also re-watch all of Mynda's videos.
Power Query
Power Pivot
Power BI
August 6, 2016
Offline
Most of what I was misunderstanding is explained in Mynda's video 6.07 Ideal Data Structure for Power Pivot.
It took a few times watching it and many attempts at my own examples, but I think it's finally clicking.
1 Guest(s)
