Filtering Out Skus that have S and their non-S conterparts

[snowstar]

Hello all,

I have  been searching how to exclude from calculations all skus that have an S and and their non-S conterparts:

SKU	Site	Date	Qty
12686	A	2024-10-31	128
12686	A	2024-11-06	80
12715	A	2024-10-29	12
12715	A	2024-10-31	10
12717	A	2024-10-31	3
12798	B	2024-10-29	9636
12798S	B	2024-10-31	9570
12799	B	2024-10-29	1992
12799	B	2024-10-31	5062
12799	B	2024-11-04	2500
12799S	B	2024-10-29	1826
12799S	B	2024-10-31	4976
12799S	B	2024-11-06	2500

I want the result to show this:

SKU	Site	Date	Qty
12686	A	2024-10-31	128
12686	A	2024-11-06	80
12715	A	2024-10-29	12
12715	A	2024-10-31	10
12717	A	2024-10-31	3

The sku is from a productable and the qty is from a fact table.

I have been trying options and its not working.

I tried with filters and creating a measure.

Test =

CALCULATE (

    [Shipped Quantity],

    KEEPFILTERS( Products[sFlag] = 1

     )

)

Any hep is appreciated!

[Gabry · Accepted solution]

Hey buddy @snowstar ,

try this

measure = sumx(
FILTER(
FILTER(Tabella, not Tabella[SKU] in

SELECTCOLUMNS(
ADDCOLUMNS(
SELECTCOLUMNS(FILTER(all(Tabella), RIGHT(Tabella[SKU],1)="S"), [SKU]), "NOs", LEFT(Tabella[SKU],5)),[NOs]) ) , not Tabella[SKU]  in  SELECTCOLUMNS(FILTER(all(Tabella), RIGHT(Tabella[SKU],1)="S"), Tabella[SKU]))
, Tabella[Qty])

and let me know 😉 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[snowstar]

Hello Resident Rocstar and Gabry!

Both answers work! 

Thank you very much!

[vojtechsima · Accepted solution]

@snowstar hello,

here's similar approach as well:

Qty Sum Measure = 
var getS_SKU = 
CALCULATETABLE(
    VALUES('Table'[SKU]),
    CONTAINSSTRING('Table'[SKU], "S")
)
var findCounterParts =
    ADDCOLUMNS(getS_SKU, "nonS", LEFT('Table'[SKU], LEN('Table'[SKU])-1))

var uniteThem = UNION( getS_SKU, SELECTCOLUMNS(findCounterParts, [nonS]))

var _result =
CALCULATE(
    SUM('Table'[Qty]),
    KEEPFILTERS(NOT 'Table'[SKU] IN uniteThem)
)

return _result

[Gabry · Accepted solution]

Hey buddy @snowstar ,

try this

measure = sumx(
FILTER(
FILTER(Tabella, not Tabella[SKU] in

SELECTCOLUMNS(
ADDCOLUMNS(
SELECTCOLUMNS(FILTER(all(Tabella), RIGHT(Tabella[SKU],1)="S"), [SKU]), "NOs", LEFT(Tabella[SKU],5)),[NOs]) ) , not Tabella[SKU]  in  SELECTCOLUMNS(FILTER(all(Tabella), RIGHT(Tabella[SKU],1)="S"), Tabella[SKU]))
, Tabella[Qty])

and let me know 😉