Calculate All only responds to grouping / implicit filtering

[christianirgens]

I'm using the following measure to calculate the expected number of incidents per department: 

ExpIncidentCount = sum(Mydata[Persons])*calculate([frequency], all (Mydata[Department]))

(i.e. using the company-wide frequency to calculate the number of incidents in the department)

This works OK in a visual grouped by department (i.e. grouping rather than explicitly filtering by department) - but when filtering for a department the all-statement seems to be ignored. 

Is this a PowerBI error or is the all-statement only supposed to respond to implicit filtering?

[Anonymous]

Hi there.

If you build your model correctly, you won't have any problems. Your model currently is not correct. Please, try to watch these first:

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA

Once you know what a good model is (think: star schema), please rebuild yours to adhere to Best Practices. Then you'll be able to write correct measures that'll work correctly in all conditions. Storing everything in one big table is a sure way to fail miserably.

Power BI does not have a problem. You have to understand first how DAX and filtering work.

If you have a good model (ONLY THEN), then you can do something similar to this:

[Person Count] = countrows( Person )

[Expected Incident Count] =
	[Person Count]
	* calculate(
		[Frequency],
		all( Person )
	)

The above works correctly on the assumption that there's a dimension called Person (could be called Employee as well) and one of the columns holds the department in which the person works.

Best
D

[christianirgens]

Thank you for looking into this.
I agree bad model design is a plausible explanation. Everything is stored in one (small:-))) table however, and the report works nicely for all other purposes. The measure I'm struggling with also works if I replace department by other dimensions. I also believe it used to work for "department" - and am therefore suspecting some sort of PowerBI bug.

[Anonymous]

Please try to remember this one golden rule of DAX programming: ALWAYS, ALWAYS, ALWAYS use a star schema, no matter how small your model is. If you want to see what can happen if you don't use it, please have a look at this:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Please read this well, let it sink in, and never again be tempted to by-pass the necessary step of creating a good star-schema model.

You'll thank me later.

Best
D

[camargos88]

Hi @christianirgens ,

Do you mean, when you filter by any department you get the same value ? 

If yes, try it:

ExpIncidentCount = sum(Mydata[Persons])*calculate([frequency], ALLSELECTED(Mydata[Department])) 

If not, show us some example.

Ricardo

[christianirgens]

Thank you for the quick reply!

When filtering for a department  "calculate([frequency], ALLSELECTED(Mydata[Department]))" changes and gives the same result as [frequency]. The intention was that this part of the formula should be unaffected by the departmet filtering. 

Changing from "all" to "allselected" doesn't help. 

Best regards

Chr.

[camargos88]

@christianirgens ,

Can you provide some example ?

Ricardo

[christianirgens]

The below constructed example illustrates the problem:

The graph and Card both shows FrequencyAll = calculate([Frequency];all(Mydata[Department])). Department is on the x-axis of the graph. I've clicked on the first department (left most bar) to show that the Frequency change from 0,17 to 0,07 - inspite of the "all-statement". If I click on the different bars the number shows the [Frequency] rather than the [FrequencyAll] . Replacing ALL by ALLSELECTED gives other numbers than [Frequency] in the card - and the numbers still change when clicking on the bars. 

The formula works as expected if I replace the "department" (in the formula and graph) by some of the other fields in the data set. I'm therefore suspecting some sort of PowerBI errror (either interaction-problems with other measures using the same field, problems with using fields with a very large number of different items or some other issue).  

[camargos88]

@christianirgens ,

Is it possible to share the pbix ? 

Ricardo