Slicing Calculated Column Sums Using Nested Relation

[Aweptimum]

I'm making a report for displaying equipment parts. Part of that is displaying the count of the parts per piece of equipment and the total cost of replacement associated with them. I have a table with the equipment names (there are duplicate names because they're associated with child data), a table that has each parts associated with their parent equipment, and a parts table that has distinct values. It looks like this: 

Machines Table		Machine-	Part Table		Part	Data Table
Machine		Machine	Part		Part	Quantity (Calculated Column)
1		1	CA		CA
2		1	CA		CB
3		1	CB		CC
4	Relation	1	CB	Relation	CD
5	Many:Many	1	CB	Many:One	CE
6		2	CC		CF
7		2	CC		CG
8		2	CC		CH
9		2	CD		CI
10		2	CD		CJ
11		3	CA		CK
12		3	CA		CL
13		3	CB		CM
14		3	CB		CN
15		3	CB		CO
		…	…		…

I have a table visual that shows the part data and a slicer that filters it using "Machine" from the Machines Table. Some machines share parts with other machines. For the quantity column, I take the count of that part from the second table, filtered by the Machine Name selected in a slicer. However, it doesn't count based on the Machine name; if I select Machine 1, which is identical to Machine 3, it returns the sum of parts for both, whether either or both are selected. It does filter the table to only show the parts inside that Machine (so something's working). The quantities need help though. However, I can't add a condition to the filter for the COUNT where Machines Table[Machine] = Machine-PartTable[Machine] (probably because they're already related).

I've tried ALLSELECTED and USERELATIONSIP but they both return the same incorrect behavior. Here's what I have:

Part Quantity=

CALCULATE(

    COUNT('Machine-PartTable'[Part]),

        FILTER(ALLSELECTED('Machine-PartTable'),

            'Machine-PartTable'[Part] = 'Part-DataTable'[Part]

        )

    )

I do apologize if this has already been answered, I couldn't find a similar enough topic. Thanks in advance for any advice/wisdom.

[Anonymous]

Would the solution in the file be what you're looking for?

Best

D

[Aweptimum]

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).

Machines And Parts Mod

[Anonymous]

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
D

[Aweptimum]

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.

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]

---

@Aweptimum 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

---

[Aweptimum]

OK, many apologies

If I may still ask, do you have suggestions for how to do it with my other requirements or am I on my own now? Because I've been staring at this for 5 days now and I'm beat. 

[Anonymous]

If you want to have any other calculations in there, you'll need to send me the file or place a link to it here. Then explain as well as possible what it is you want. I'll then try to implement it.

Best
D

[Anonymous]

Hi there. You said above that the model worked OK with the changes you'd made. What are the other requirements you have?

Best
D