Slicing Calculated Column Sums Using Nested Relation

---

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

---