Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
I do apologize if this has already been answered, I couldn't find a similar enough topic. Thanks in advance for any advice/wisdom.
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).
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 !
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |