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 al...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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.

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

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.