topic Dax measure Count with filter in DAX Commands and Tips

Dax measure Count with filter

Morrison — Mon, 05 Sep 2022 12:09:44 GMT

Hello,

I would like to count the number of stores with YTD YOY sales <0. I have created the following measure but the result is not correct. Can you help me correct it please. As a reference date, I entered the date of 04 09 2022 in the filters.

Number of Store in L = calculate(DISTINCTCOUNT('Table Sales'[Store Key]), FILTER('Table Sales',[Sales Net Amount YTD YoY] < 0))

Store	Sales Net Amount YTD	Sales Net Amount Y-1 YTD	Sales Net Amount YTD YoY	Count Store in Lose
A	€ 1.110	€ 1.037	€ 73	1
B	€ 1.998	€ 1.976	€ 22
C	€ 656	€ 671	-€ 15
D	€ 799	€ 795	€ 4
E	€ 523	€ 518	€ 6
F	€ 488	€ 457	€ 30
G	€ 714	€ 827	-€ 113
H	€ 292	€ 262	€ 30
I	€ 478	€ 442	€ 37

Re: Dax measure Count with filter

m3tr01d — Mon, 05 Sep 2022 12:33:32 GMT

Hi Morrison ,

the issue is with your Filter statement. Instead of iterating over Sales, it should be iterating over distinct values of Store key.

Maybe you can try this version instead?

Number of Store in L = COUNTROWS( FILTER( VALUES('Table Sales'[Store Key]), [Sales Net Amount YTD YoY] < 0 ) )

Re: Dax measure Count with filter

Morrison — Mon, 05 Sep 2022 14:06:35 GMT

Hi m3tr01d,

thx for the solution 🙂