cancel
Showing results for
Did you mean:
Highlighted
Anonymous
Not applicable

## Slicer - Filter only on one data in matrix

Hello,

I user matrix to calculate the sum of towns sales by year.

My dataset is :

 Année Libelle Mois num Mois Lib Montant HT 2018 Poitiers 1 Janvier 50 2018 Poitiers 1 Janvier 100 2018 Poitiers 1 Janvier 200 2018 Poitiers 2 Février 50 2018 Poitiers 2 Février 150 2018 Poitiers 2 Février 256 … 2018 Poitiers 11 Novembre 126 2018 Poitiers 11 Novembre 147 2018 Poitiers 11 Novembre 644 2018 Poitiers 12 Décembre 456 2018 Poitiers 12 Décembre 236 2018 Poitiers 12 Décembre 654 2019 Poitiers 1 Janvier 50 2019 Poitiers 1 Janvier 100 2019 Poitiers 1 Janvier 200 … 2019 Poitiers 6 Juin 50 2019 Poitiers 6 Juin 100 2019 Poitiers 6 Juin 200

I created 2 measures to calcultate the sum of sales :

```CA_2018 = CALCULATE(
SUM(Data[Montant HT]);
FILTER(Data;Data[Année ]=MIN(Data[Année ])
)
)```

To calculate the sum of sales of min year (2018).

```CA_2019_1 = IF (HASONEVALUE(Data_test[Année ]);
BLANK();
CALCULATE(
SUM(Data_test[Montant HT]);
FILTER(Data_test;Data_test[Année ]=MAX(Data_test[Année ])
)
))```

To calculate the sum of sales of max year 2019.

I created a slicer to dynamically filter months lib.

But if i modify slicer selection, there is an update of 2018 calculate.

I would like that 2018 calculation not to be affected.

If i add the year with month, that's ok but i prefer don't use this solution.

Thanks

Yvon

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

Hi @Anonymous ,

You can use the following DAX to create measure that are not effected by month slicer, but the Name Column in table visual will still effected by the month. But the value of 2018 is always the full year.

```SUM_2018 =
SUMX (
FILTER (
ALL ( 'Table' ),
AND (
'Table'[Year] = MINX ( ALL ( 'Table' ), [Year] ),
[Name] = SELECTEDVALUE ( 'Table'[Name] )
)
),
[Value]
)```

If you want to see all the name in this table always, just turn off the effect by this slicer.

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Highlighted
Community Support

Hi @Anonymous ,

You can use the following DAX to create measure that are not effected by month slicer, but the Name Column in table visual will still effected by the month. But the value of 2018 is always the full year.

```SUM_2018 =
SUMX (
FILTER (
ALL ( 'Table' ),
AND (
'Table'[Year] = MINX ( ALL ( 'Table' ), [Year] ),
[Name] = SELECTEDVALUE ( 'Table'[Name] )
)
),
[Value]
)```

If you want to see all the name in this table always, just turn off the effect by this slicer.

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Anonymous
Not applicable

Thanks

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors