DAX Measure evaulation context issue with related table

[Anonymous]

Hi all, I'm having an issue with a Measure when I introduce data from a related table, so let me explain (the second time, after losing my first post):

I have a Usage table with usernames and total hours used:

Username	Total Hours
Jane	20
Joe	50
Sally	10
Jeff	20

I have a simple measure that calculates the Totals Hours as a percentage of the grand total:

Hrs % of GT = DIVIDE([Total Hours], CALCULATE([Total Hours], ALLSELECTED(Usage)))

I have another Users table that has some information about the users, for instance their office, related to the Usage table by [Username] Cross Filter Direction is single from Users to Usage:

Username	Office
Jane	Boston
Joe	New York
Sally	New York
Jeff	Boston

The result is I can create a simple Table or Matrix visual that I can Slice on, and get something like this:

Office	Username	Total Hours	Hrs % of GT
New York	Joe	50	83%
New York	Sally	10	17%

I have another table Transactions, that I Filter/Slice to a single row, that looks like this:

Product	Cost
ProductName	$100

I created a simple measure that sums the Cost column, in the case of the filtering and slicing that measure returns the single value:

Sum of Transactions = SUM(Transactions[Cost])

Lastly I created a third measure called distributed cost:

Distributed Cost = [Sum of Transactions] * [Hrs % of GT]

If I create a table visual like, this, it seems to work properly:

Username	Total Hours	Hrs % of GT	Distributed Cost
Jane	20	20%	$20
Joe	50	50%	$50
Sally	10	10%	$10
Jeff	20	20%	$20

The problem comes, when I attempt to introduce Office to the Table (or Matrix) I lose the Disitrubuted Cost values. What is particulary strange is I don't lose all the values, only sum and the Grand Total for the column remains correct. The ultimate goal in all of this is to filter the above table/matrix on for example: Hrs % GT greater than 10%

And have the measure re-compute to evenly distribute the cost based on the new GT, so that it might look like this:

Office	Total Hours	Hrs % of GT	Distributed Cost
Boston	40	45%	$45
New York	50	55%	$55
Total	90	100%	$100

It seems like this should work, because I want the measures to respect the filter/sliced context, but I don't understand why I'm seeing the behavior I'm seeing when I introduce the related data of Office, particularly since the Hrs % of GT measure works properly. What am I missing; do I need to do something with 'Related' for the cost disitribution measure to work properly? Something with applying the proper filter context?

Thank you!

[Anonymous · Accepted solution]

Hello all,

I think, after building a "clean" test model I've discovered the issue at hand. I have a many-to-many relationship which is causing the issue I'm seeing. My clean model has only many-to-single relationships. This would seem to make sense to me based on what I'm seeing. Based on what I've described does this make sense to everyone?

[edhans]

Yup. In general many-to-many should be avoided unless you know exactly how it will work and there is no other way. Glad you found the issue and were able to get it to a one-to-many relationship.

[Anonymous]

if only it were that simple... my "clean" model was for the purposes of troubleshooting and to post here. The real model retains the many to many and I know why it's there and what the underlying issues are, but "fixing" it becomes its own project unto itself. This was just a fall on effect that I didn't recgonize right away.

Joy...

Thanks all!

[edhans]

In that case @Anonymous you must read this fantastic artilcle on many-to-many relationships. 

[Anonymous]

@edhans thanks for the tip

[amitchandak]

Try new measures like

Sum of Usage = calculate(SUM(Usage[Total Hours]),all(Users))

Hrs % of GT = DIVIDE(sum(Usage[Total Hours]), [Sum of Usage ])

Distributed Cost =
var _cost = maxx(product,product[Cost])
return
[Hrs % of GT] * _cost

As join between user and usages has been assumed on usename /id

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

Hi,

So the measures you suggested seem to work the same as the previous measures. Were you assuming a relationshp between the Transactions table and the other tables?

Thanks,

-R

[amitchandak]

I just assumed the relationship between the first two tables in your list having username.

 If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

Thanks...

Yeah, I'll see about stripping it down tomorrow, or what I can do. Its using DB sources, bunch of extra data too, so a bit challenging, not impossible.

[amitchandak]

Data what you have given above, if you put that in excel and configure in power bi in the same table structure you have that can help. I can do that, just want to know table relations.

[Anonymous]

I'll build a sample model; at this point, probably a good thing to do given the issue I've run into. It will be pretty easy to just duplicate the structure using Excel tables and throw in some "fake"/sanitized data.

[edhans]

A visual of your model would help. I may have missed it but didn't see how you are relating Transactions to the rest of the model, but I suspect it is not filtering the rest of the model. Meaning, whatever you are relating Transactions to, Transactions is the Many of a One to Many.

If that is correct, see what happens if you modify this measure:
Distributed Cost = [Sum of Transactions] * [Hrs % of GT]

Change it to this:

Distributed Cost =
CALCULATE(
    [Sum of Transactions] * [Hrs % of GT],
    CROSSFILTER ( Transactions[SomeField], TheOtherTable[KeyField], BOTH )
)

What CROSSFILTER() does inside of CALCULATE is turns a single directional filter to bi-directional for that measure only, which is what you want. You can turn on bi-directional in the relationship itself, but that should be avoided at all costs as it can introduce unexpected issues or simply break things as the model gets more complex.

If that is not helpful, please post a pic of your model from the model view and how Transactions is related to another table.

[Anonymous]

Hi,

Thanks for the quick response. At the moment there is no relationship between the Transactions table and the Usage and other associated tables. The reason being I brought the Transactions table in to explicitly slice down to a single transaction, and use its value as a constant for the distirbution calculations. In the future I might want to slice to a new transaction and then re-calculate a new disitrubtion based on new usage data.

I could create a relationship between the Usage data and the Transactions based on "Product" if you think that might help? I assumed that since I was basically using the value as a constant a relationship wasn't neccessary, but perhaps it is?

[edhans]

---

@Anonymous wrote:

Hi,

Thanks for the quick response. At the moment there is no relationship between the Transactions table and the Usage and other associated tables. The reason being I brought the Transactions table in to explicitly slice down to a single transaction, and use its value as a constant for the distirbution calculations. In the future I might want to slice to a new transaction and then re-calculate a new disitrubtion based on new usage data.

I could create a relationship between the Usage data and the Transactions based on "Product" if you think that might help? I assumed that since I was basically using the value as a constant a relationship wasn't neccessary, but perhaps it is?

---

Then I'm confused. You cannot filter anything with a disconnected table via a slicer, and to use it with measures you have to use measures like TREATAS() to create a virtual relationship. Again, I'd need to see a more compressive model of what you have and are trying to accomplish.

[amitchandak]

You can filter when tables are not connected. But grouping data across the table and then provide data at a different level will require lots of stuff