cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GreenMo
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

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.

View solution in original post

Super User IV
Super User IV

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3

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.

View solution in original post

Super User IV
Super User IV

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

GreenMo
Frequent Visitor

Re: If function to ignore row context

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors