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 !
Anonymous wrote:
Thanks 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.
>> This is why you should always tell us all details that are important in the model. It looks like you did not explain the whole, only one part of the problem.
As for cross-filtering, in my actual report, the slicer has no effect on the quantities whether cross-filtering is turned on or off.
>> That's not something I wasn't expecting. To the contrary.
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?
>> No, you cannot populate a column using a measure but what you can do is you can FILTER OUT rows you don't want to see in any visual by means of a measure. If a measure for a particular item returns BLANK, the item will be hidden by the visual.
Also, HASONEVALUE() is returning true and giving accurate quantities even though my slicer has multi-select enabled. Is that intended behavior?
>> HASONEVALUE is not looking at your slicer but at WHAT IS VISIBLE IN THE CURRENT CONTEXT (this always happens with any DAX function). This is very different from what you think is happening. You can have several items selected in a slicer but if the rows in the table visual filter just one item, then the function returns TRUE.
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.
>> Of course it can. You just did not show me all the details. If you had, I would have designed it so that it would do all you need.
I'm sorry if I'm frustrating you, I appreciate your insights Anonymous !
>> Not at all. If you had told me ALL the requirements in advance, you would already have a full working solution.
Best
D