Error in CALCULATE ALL

[christianirgens]

I'm experiencing erratic results from CALCULATE(measure, ALL(field)) statements when using page filters to filter other fields with a large number of distinct values. The problem also occurs if positive fields are filtered with ">-1". The problem is fixed/reduced if the field is replaced by a rounded version (i.e. fewer distict values), but can reoccur when using several page-filters. Example attached. 

A serious bug?

[christianirgens]

I was not able to attach the pbix file - but try the following:

Generate a data set containing 100 000 rows with the following: 

RandomValue=INT(RAND()*10000)

RandomRounded=ROUND(RandomValue,-3)

Group=INT(RAND()*3)

Value=INT(RAND()*10000)

Then construct the following measure 

SumValueIgnoringGroup = calculate(sum(Data[Value]),all(Data[Group])) and use this in a Card. 

Add chart using the group-field - and see that the Card is unaffected by filtering (as it should be). 

The try using the pagefilter to filter for RandomValue>-1  and see how the Card is erronously affected when you click on the groups. 

If you filter RandomRounded >-1 the Card works ok. 

[christianirgens · Accepted solution]

This was answered in https://community.powerbi.com/t5/Issues/CALCULATE-ALL-BUG/idi-p/1283779

(I.e. this is an "error by design"). 

[harshnathani]

Hi @christianirgens ,

Value of RAND() keeps changing with every refresh.

Not sure what the problem statement for you is. 

Incase you are looking for generating nos. you can have a look at this articles.

https://www.sqlbi.com/articles/generating-a-series-of-numbers-in-dax/

Regards,

Harsh Nathani