How to write Filter and AllExcept if I have a couple of parameters to filter?

[echow]

HI,

I need help on creating a DAX for filtering the following result in column E to H.

Basically, I need to create a 'Filter All' by Age and the year. And for the Age I would like it to ignore the calculation for the band Age_99 and be able to differentiate between two company type (A & B)

For Column F(Filter All), I believe the DAX language is something like this

Filter All= CALCULATE([Total Count],

FILTER(ALLEXCEPT(Table1,Table1[Company Type]),

Table1[AGE_ID] <> "AG_99"))

But how do I go about writing the filter  for column H to K if I would like to have the result filter by year.

Appreciate if someone can help on this.

[tex628]

I think something like this should work:

Filter 2017 =
IF( SELECTEDVALUE(Calendar[Year]) = 2017 ,
CALCULATE([Total Count],
FILTER(ALLEXCEPT(Table1,Table1[Company Type]),
Table1[AGE_ID] <> "AG_99"),
Calendar[Year] = 2017
),
BLANK()
)

Filter 2017/2018 =
IF( SELECTEDVALUE(Calendar[Year]) = 2017 || SELECTEDVALUE(Calendar[Year]) = 2018 ,
CALCULATE([Total Count],
FILTER(ALLEXCEPT(Table1,Table1[Company Type]),
Table1[AGE_ID] <> "AG_99"),
Calendar[Year] = 2017 || Calendar[Year] = 2018
),
BLANK()
)

[echow]

Let's just say I do not want to explicitly call out the year, but it is a year filter that will aggregate these numbers accordingly.

[tex628]

Alright. In that case i will need to know how the selection logic is supposed to work. Could you give some examples of displayed columns with different selections on year? 

[echow]

Basically, rather than me specifying the year, I will allow the user to choose the year. 

I wrote something based on what you have but I got the most outer left table output.

What I would want is the most outer right table output

Total Age Count =

VAR CurrentYear = SELECTEDVALUE(Table1[YEAR])

RETURN

CALCULATE([Total Count],

FILTER(ALLEXCEPT(Table1,Table1[CompanyType]),

Table1[AGE_ID] <> "AG_99"),

Table1[YEAR] = CurrentYear)

[v-joesh-msft]

Hi @echow ,

I think what you need is the following measure:

Total Age Count =
CALCULATE (
    [Total Count],
    FILTER ( ALLEXCEPT ( Table1, Table1[CompanyType] ), Table1[AGE_ID] <> "AG_99" )
)

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

[echow · Accepted solution]

Thanks @v-joesh-msft I think I have it figured out, that is to include the year parameter in the filter allexecept.

Total Age Count =
CALCULATE (
    [Total Count],
    FILTER ( ALLEXCEPT ( Table1, Table1[Year], Table1[CompanyType]),
    Table1[AGE_ID] <> "AG_99" ))