Forum Discussion

jacschZA's avatar
jacschZA
Advocate I
4 years ago
Solved

DAX remove filter but keeps the same filter..

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 relat...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    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:

     

     

MFelix's avatar
MFelix
Super User
4 years ago

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's avatar
    jacschZA
    Advocate I
    4 years ago

    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