Forum Discussion
Slicing Calculated Column Sums Using Nested Relation
Would the solution in the file be what you're looking for?
Best
D
Somewhat, yes. However, in the table, I'm trying to achieve:
1. Each part associated with a machine to be present
2. The quantity of that part in the selected machine
So I'm only doing this with a single slicer based on the Machine Name. The problem I'm having is that my slicer doesn't seem to be filtering down to the Part table, only the MachineToPart table. I changed your file to illustrate and attached it with a drive link - I ungrouped rows in MachineToPart, changed your measure to use COUNT(), and deleted the Part slicer. Somehow, these changes have got it working as I desired, but I have no clue why. It's against everything I've found to be consistent in PowerBI so far (and I wouldn't mind an explanation).
- Hi there.
Well, you have changed only the MachineToPart table but the change is completely inessential from the point of view of functionality. What you've done is you've only expanded the table making it bigger. But the size will be felt only when the table has millions of rows.
I don't understand why you should expand the table since the compact one returns exactly the same results.
Your COUNT( MachineToPart[Part] ) is achievable through COUNTROWS( MachineToPart ).
I don't understand what's really different from the solution I originally posted. I can't see any difference.
The selection of a machine name from the dimension does not filter to the Part dimension because for this to be the case you'd need to change the connection between MachineToPart and Part to be two-way. But two-way cross-filtering is dangerous and should not be used unless strictly necessary.
Best
DThanks for your reply,
I'm sorry it appears weird, it's just that there is one column in the MachineToPart table that stores the Part's "position", so I was trying to avoid grouping the data. That being said, the rows in MachineToPart should be ~200 total at most since it's a user-defined table so that's why I'm ok with keeping it expanded in some way.
As for cross-filtering, in my actual report, the slicer has no effect on the quantities whether cross-filtering is turned on or off.
Now, your solution does work, however I don't understand how. I didn't think you could populate a column in a BI table using a measure (I thought calculated column was the way to go). Is there a resource that can explain that? Also, HASONEVALUE() is returning true and giving accurate quantities even though my slicer has multi-select enabled. Is that intended behavior?
Lastly, one thing I wanted to be able to do on top of this that keeps me from committing to your solution is that I need to sum the quantities for each part to determine total cost, per Machine, of Parts. Even though your Quantity measure populates as a column, it can't be summed up with another measure.
I'm sorry if I'm frustrating you, I appreciate your insights Anonymous !