If function to ignore row context

[Anonymous]

Hey all!

I am trying to create a measure using the IF function.

The measure is supposed to calculate a sales provision based on a departments performance on hitrate, but on employee level.

So i got as far as getting the  if function to work on a deparment level, but as soon as i put in the empoyees as rows instead of the departments, the measure starts to calculate the provision based on the employees hitrate. Is there a way to connect the employee to the department they belong, and based on that calculate the the provision.

What i want the measure to do is, to calculate as following:

if department A reaches hitrate X, the employee can get provision Y.

Is this even possible with Dax? i tried playing around with the use relation function but i dont know if that is the correct way to tackle this issue.

[MattAllington · Accepted solution]

Yes it is possible, but DAX formulas always depend on the table and relationship structure. You haven’t provided any info about your setup, so it is not possible to give you an accurate answer.

In short, this has nothing to do with row context, it is filter context. When you put department into a table, the department filters the underlying data and gives you the result. When you add employee, a new filter context is added to employee preventing the department level calc working. It is a simple fix - just remove the filter from employee. Something like this. 

CALCULATE([measure],ALL(table[employee]))

[Ashish_Mathur · Accepted solution]

Hi,

Since you will have various combinations of departments, hit rates and provisions, it will be much better to table this data in a 3 column Table and then build relationships.  We can then write measures to get your desired result.

[MattAllington · Accepted solution]

Yes it is possible, but DAX formulas always depend on the table and relationship structure. You haven’t provided any info about your setup, so it is not possible to give you an accurate answer.

In short, this has nothing to do with row context, it is filter context. When you put department into a table, the department filters the underlying data and gives you the result. When you add employee, a new filter context is added to employee preventing the department level calc working. It is a simple fix - just remove the filter from employee. Something like this. 

CALCULATE([measure],ALL(table[employee]))

[Anonymous]

Thanks 🙂 i got it to work, i amde a combination of the 2 replys