Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

DAX calculation head scratcher

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.

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

1 Reply

  • Vvelarde's avatar
    Vvelarde
    Community Champion

    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.