Asking About Filter

[pociners]

Hi Everyone It's me :D

I wanna ask something simple,
This is my data:

I create 3 simple calculated measure:

[B]Sales[/B]:=SUM([Qty])

[B]Stock[/B]:=1000

[B]Stock End[/B]:=CALCULATE([Stock]-[Sales],FILTER(Table1,Table1[ID]>3))

and this is the result from pivot:

Well, based on my filter (Table[ID]>3), Stock end where ID <= 3 should be BLANK, but it shows 1000, why?
Can you help me?

Thanks,

 

[Matt Allington]

Your table filter portion of the formula only filters the table, not the contant "stock=1000". So your filter "hides" the values for id 1,2 &3 and hence they each = 0. 1,000 minus zero = 1,000.

You could do something like this.

=sumx(table1,if(table1[id]>3,[stock]-sum(Table1[Qty]))

but it as a warning, DAX can be deceivingly complex and I am not really sure why you would want to do what you are saying. If it is to learn, then sure. But if this is part of some end solution, then I am not sure it makes sense.

 

[pociners]

Hey Matt,

Thanks again for your solution..