DAX calculation head scratcher

[Anonymous]

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.

[Vvelarde · Accepted 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.