DAX remove filter but keeps the same filter..

[jacschZA]

Hi DAX community!

Hope someone can assist me with this one. No doubt it is possible, just been racking my brain and not winning.

So I have a classic inventory dataset. Excpet with sizes related to an Item.

DimProduct, DimStore, FactSales.

Note that the Product has sizes to a ProductCode. And not all products have the same set of sizes to it. One might have small to XLarge, while another might have only Small and Medium.

I want to calcuate the below RED section, but the calc I need to use, has to remove the SizeCode, then keep it as well. So as, in the below example, RED TEXT, not to show the last size XL. Because ProductCode BBBB does not have a size XL, only S-L. 

I would like to calculate so that I return the below table, where the Grand Total is 53 for StoreA, and not 64, which includes the size XL. Because the selected Product BBBB, only has size S,M,L.

StoreCode	L	M	S	Grand Total
StoreA	53	53	53	53
StoreB	47	47	47	47
StoreC	53	53	53	53
StoreD	59	59	59	59
StoreE	56	56	56	56
Grand Total	268	268	268	268

View of the DimProduct and DimStore and Fact Sales table:

Thanks!

Jacques

[v-yalanwu-msft]

Hi, @jacschZA ;

I tried to restore your original model and requirements, but failed. Based on the information you provided, you can try the following :

Sales Qty for all Items Total =
CALCULATE (
    [Sales Qty for all Items],
    FILTER ( ALL ( 'Fact' ), [Store] = MAX ( 'Fact'[Store] ) )
)

 If not, can you provide a simple file after removing sensitive information?It makes it easier to give you a solution.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix · Accepted solution]

Hi @jacschZA ,

Create new table with only the Item codes:

Now add the following measure to your model:

Size Total = 
VAR sizeTable =
    FILTER (
        ALL ( DimProduct ),
        DimProduct[ItemCode] IN VALUES ( Products[ItemCode] )
    )
VAR Sizeselction =
    SELECTCOLUMNS ( sizeTable, "Size", DimProduct[Size] )
RETURN
    CALCULATE (
        SUM ( 'Fact'[Qty] ),
        FILTER ( ALL ( DimProduct ), DimProduct[Size] IN Sizeselction )
    )

Has you can see below when you select BBBB the result is only 53:

[jacschZA]

Miguel!

Yes this did work! Thanks so much.

I actually ended up also finding a solution before I saw yours, but they both seem to work. Thanks for the help!

Size Total=
CALCULATE (
    SUMX (
        CALCULATETABLE (
            SUMMARIZE (
                DimStores,
                DimStores[StoreCode],
                "AMT",
                    CALCULATE (
                        [Sales Qty for all Items],
                        ALLEXCEPT ( DimProduct, DimProduct[ItemCode], DimProduct[Size] )
                    )
            ),
            DimProduct[ItemCode] = SELECTEDVALUE ( DimProduct[ItemCode] )
        ),
        [AMT]
    ),
    REMOVEFILTERS ( DimProduct[Size] )
)

Regards,

Jacques