cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GreenMo Frequent Visitor
Frequent Visitor

If function to ignore row context

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.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: If function to ignore row context

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]))



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Super User

Re: If function to ignore row context

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.

3 REPLIES 3
Highlighted
Super User
Super User

Re: If function to ignore row context

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]))



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Super User

Re: If function to ignore row context

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.

GreenMo Frequent Visitor
Frequent Visitor

Re: If function to ignore row context

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

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 259 members 2,604 guests
Please welcome our newest community members: