Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jacschZA
Advocate I
Advocate I

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 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. 

jacschZA_0-1646202059846.png

 

 

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.

 

StoreCodeLMSGrand Total
StoreA53535353
StoreB47474747
StoreC53535353
StoreD59595959
StoreE56565656
Grand Total268268268268

 

 

View of the DimProduct and DimStore and Fact Sales table:

 

jacschZA_1-1646202466581.png

   

jacschZA_2-1646202477712.png

 

 

 

jacschZA_3-1646202492116.png

 

jacschZA_4-1646202512757.png

 

 

Thanks!

Jacques

 

   

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @jacschZA ,

 

Create new table with only the Item codes:

MFelix_0-1646388624958.png

 

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_1-1646388681110.pngMFelix_2-1646388692642.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

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
Super User
Super User

Hi @jacschZA ,

 

Create new table with only the Item codes:

MFelix_0-1646388624958.png

 

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_1-1646388681110.pngMFelix_2-1646388692642.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.