First of all thanks for your support related to earlier posts.
In this case I have a question related to relationships. I simply have project table with dollars amount and owners table. If you look at project 123 it has two accounts 811111 for an amount of $100 and 811112 for an amount of $200.
The owners are two different persons X & Y. I setup some distinct tables for project and accounts as per the Power bi Attached but once I create the matrix I drill on project 123 and I get double a total amount of $300 for X and $300 for Y. Once I drill further I get the correct amount on each.
@Zizo,
You have done a good job of setting up this data model so far. I went ahead and created a table with DistinctOwners = DISTINCT(Owners[Owners] )
Though might be better to do this type of thing in Power Query, but DAX works too.
Then using that table ( along with your other ones) I set up the data model as such:
It’s important to use the columns from those 3 Distinct tables for all your filters, slicers, rows, etc in the report. With that in mind, here’s the final output:
And this is the measure that got us there: Total Amt Correct = CALCULATE( [Total Amount], Owners)
With Total Amount being a simple sum: Total Amount = sum( Projects[Amount] )
Without getting too long winded, we are using the concept of expanded tables here. Since owners is on the many side of a 1:M relationship, Owners includes its’ own columns plus those of the three distinct tables. This is why it’s imperative to use the columns from the distinct tables as your filters. So what is happening is that the AccountDistinct and ProjDistinct are filtering both the Owners and Project tables. Which would be just fine and work as you would expect, but you also wanted an Owners filter. Which is why we created that new table. DistOwners filters the Owners table only. So we need to “send” that filter down to Projects. Which is why we use the Owners table ( which is filtered) as the filter for calculate…
Expanded tables arent the easiest to explain over a forum, so hopefully that make some sense!?
Thank you guys for jumping in and provide very timely support. I was really struggling the whole day yesterday with this issue.
First of all I always create measure, this is a really simple model to illustrate the issue.
Nick the idea of creating distinct table for owners is excellent and should be the cleaner way to go. Before I slept yesterday I was able to come with something that I would like to share . I created an inital measure
Owners AMount = SUMX(Projects,Projects[Amount])
Then I created another one on top of it.
