Change item name if hitting certain account

DAX DAX Calculations
1

When an item sales and is linked to a certain acct, I need it to show up as shipping, else it’s name. I need the most efficient way as there are 100+ millions rows in the trans-line table. Maybe this is easier then I’m making it out to be but I need help. The sample has the results I’m looking for. Thanks for your help.
Sample.pbix (38.4 KB)

2

Hi @Usates,

It’s not a perfect fit for your requirement but I think you could translate the techniques from this article and apply it to your situation. I hope this is helpful.

3

I can’t get this solution to work with my issue. Sorry.

4

Hi @Usates,

Give this a go. Create a dax table Item Names
image

Create a 1:M in the model
image

Create a Sales with Shipping measure

Sales w. Shipping = 
VAR _SalesOfAll =
    CALCULATE( [Sales],
        REMOVEFILTERS( 'Item Names' )
    )
VAR _AllNonShipping =
    CALCULATE( [Sales],
        REMOVEFILTERS( 'Item Names' ),
        LEFT( COA[Number], 2) <> "45"
    )
RETURN

IF( NOT( ISINSCOPE( 'Item Names'[Items] )), _SalesOfAll,
        VAR _Sales = CALCULATE( [Sales], LEFT( COA[Number], 2) <> "45" )
        VAR _IsShipping = SELECTEDVALUE( 'Item Names'[Items] ) = "Shipping"
        RETURN
    IF( NOT( _IsShipping ), _Sales,
        _SalesOfAll - _AllNonShipping
    )
)

with this result
image

Here’s your sample file: eDNA Other Shipping.pbix (45.0 KB)
I hope this is helpful

1 Like
5

Thank you. Works perfectly.