SUM with ALL, CONTAINS and Filter

[Anonymous]

I am trying to write a dax formula in Tabular model to calculate the SUM of CarParc colum.  To calculate the SUM I want to reatain the Filter added with Contains but ignore all other filters. Please help to know ho to achieve. Below formula  with ALL gives some different result whihc is not expected.

SUMX(
FILTER( ADDCOLUMNS(
SUMMARIZE(Fact_Planned_CarParc; Fact_Planned_CarParc[Year]; Fact_Planned_CarParc[FactoryCode]);
"Age"; selectedyear - Fact_Planned_CarParc[Year]
;"totalcount"; CALCULATE(SUM(Fact_Planned_CarParc[CarParc]); ALL(Fact_Planned_CarParc[Year])));
CONTAINS(Dim_AgeInterval;Dim_AgeInterval[AgeId];[Age]))
; [totalcount])

[amitchandak]

@Anonymous ,

what you want to filter is not clear

SUMX(
FILTER( ADDCOLUMNS(
SUMMARIZE(Fact_Planned_CarParc; Fact_Planned_CarParc[Year]; Fact_Planned_CarParc[FactoryCode]);
"Age"; selectedyear - Fact_Planned_CarParc[Year]
;"totalcount"; CALCULATE(SUM(Fact_Planned_CarParc[CarParc]); ALL(Fact_Planned_CarParc[Year])));
filter(Dim_AgeInterval, CONTAINS(Dim_AgeInterval;Dim_AgeInterval[AgeId];[Age])))
; [totalcount])

You can add filter clause like Fact_Planned_CarParc[Year] in allselected(Fact_Planned_CarParc[Year])

Fact_Planned_CarParc[Year] in values(Fact_Planned_CarParc[Year])

[Anonymous]

@amitchandak 

I am working on the older version of SSAS. It does not support IN.

I want to calculate below and ignore any other filter applied on Fact_Planned_Carparc table

SUMMARIZE(Fact_Planned_CarParc; Fact_Planned_CarParc[Year]; Fact_Planned_CarParc[FactoryCode]; "Age"; selectedyear - Fact_Planned_CarParc[Year]
;"totalcount"; CALCULATE(SUM(Fact_Planned_CarParc[CarParc])));
CONTAINS(Dim_AgeInterval;Dim_AgeInterval[AgeId];[Age])

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Try to modify as below:

SUMMARIZE(ALL(Fact_Planned_CarParc); Fact_Planned_CarParc[Year]; Fact_Planned_CarParc[FactoryCode]; "Age"; selectedyear - Fact_Planned_CarParc[Year]
;"totalcount"; CALCULATE(SUM(Fact_Planned_CarParc[CarParc])));
CONTAINS(Dim_AgeInterval;Dim_AgeInterval[AgeId];[Age])

Best Regards,
Kelly

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