Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I've got a case that I just can't quite wrap my head around. I have 2x tables (related on BasketID) giving me information, and in the end need to calculate the total possible capacity for all Functional baskets (whether in table 1 or 2). I've tried multiple different approaches but can't quite get it.
Table 1
Pallets BasketID Availability
Pallet1 1 Functional
Pallet1 2 Functional
Pallet2 3 Functional
Pallet3 5 Broken
Table 2
Contents BasketID Capacity
Toys 1 5
Cars 1 5
Ponys 1 5
Dolls 3 4
Brushes 4 9
Because basket 1 & 2 are on the same Pallet, I know they have the same capacity, however basket 2 is not filled so it doesn't show in table 2. Basket 4 is not on a pallet, however we need add it's capacity to the total as well. I'm struggling to get a total capcacity mostly because every formula I come up with filters out basket 2 and it's capacity.
With the data I have, I could calculate a new column for table 1 which shows the capacity, and I could sum that up, however I'm unsure how I'd go about summing the capacity of the remaining baskets in table 2 without duplicating some of the info already calculated in table 1.
From the sample above, I'd be needing to get a calculated value of 23 (basket 1, 2, 3, 4)
Any help would be appreciated.
Solved! Go to Solution.
@Anonymous
My First try to solve this:
The tables are not related. (Contents & Pallets)
Create a New Table - (Modeling-new Table)
TotalBaskets = DISTINCT ( UNION ( VALUES ( Pallets[BasketID] ), VALUES ( Contents[BasketId] ) ) )
Next Step: Related the Tables
In this new Table:
Add two Columns:
Pallets = CALCULATE ( VALUES ( Pallets[Pallets] ) )
Capacity = IF ( TotalBaskets[Pallets] <> BLANK (), CALCULATE ( VALUES ( Contents[Capacity] ), FILTER ( Pallets, Pallets[Pallets] = TotalBaskets[Pallets] ) ), CALCULATE ( VALUES ( Contents[Capacity] ) ) )
Finally Just insert a Card with Sum Of Capacity.
@Anonymous
My First try to solve this:
The tables are not related. (Contents & Pallets)
Create a New Table - (Modeling-new Table)
TotalBaskets = DISTINCT ( UNION ( VALUES ( Pallets[BasketID] ), VALUES ( Contents[BasketId] ) ) )
Next Step: Related the Tables
In this new Table:
Add two Columns:
Pallets = CALCULATE ( VALUES ( Pallets[Pallets] ) )
Capacity = IF ( TotalBaskets[Pallets] <> BLANK (), CALCULATE ( VALUES ( Contents[Capacity] ), FILTER ( Pallets, Pallets[Pallets] = TotalBaskets[Pallets] ) ), CALCULATE ( VALUES ( Contents[Capacity] ) ) )
Finally Just insert a Card with Sum Of Capacity.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |