cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How do relationships work with ALLEXCEPT in a CALCULATE function?

Background:

  • I have a table of employees.  One column represents the reporting month (HEADCOUNT[Month])
  • Monthly Average Headcount = Count(HEADCOUNT[EMPLOYEE]) / DISTINCTCOUNT(HEADCOUNT[Month])

 

I am running into situations where a user can slice the data so that there may not be any employees for a particular month.  Since there is no row for that month due to highlighting/filtering, the month is not included in DISTINCTCOUNT(HEADCOUNT[Month]).

So if Jan - Nov we have 1 employee, then Dec there are none, DISTINCTCOUNT(HEADCOUNT[Month]) = 11 instead of 12 for the year.

 

To rectify, I'm trying to use ALLEXCEPT in a CALCULATE function so that it will ignore all filters except the filters applied to the HEADCOUNT[Month]. 

 

Here is my formula:

Number of Months = CALCULATE(
			DISTINCTCOUNT(HEADCOUNT[Month]),
			allexcept(HEADCOUNT,HEADCOUNT[Month]))

This formula is still returning 11 months.

 

I believe the problem is that my report has a relationship between HEADCOUNT[Month] and a column in a DateKey table which has all the Fiscal Quarters defined.  I then use the DateKey column to filter the report via a date slicer.

 

I figured this out because if I replace the value in the date slicer with HEADCOUNT[Month] directly, I get 12 months for the above formula.

 

So my question is, how can I instruct CALCULATE to ignore all filters, except the one implicitly provided through the DateKey - HEADCOUNT relationship?

5 REPLIES 5

Re: How do relationships work with ALLEXCEPT in a CALCULATE function?

Why don't you count the months from your calendar table and not the data table?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Re: How do relationships work with ALLEXCEPT in a CALCULATE function?

My calendar table has all dates of the year. I suppose I could just add a column for month-year and count that disntictly. Or come up with a more complicated measure which does that in its calculation.

I actually did try using DATEDIFF by months, but it behaves incorrectly for this purpose. Jan1 - Feb28 reports 1 month instead of 2.

Workarounds aside, is there anything wrong with my thought process for CALCULATE? I dont understand why it would still return 11. If there were no filters recognized for HEADCOUNT[month], shouldnt it return a count for the entire table (3 years worth of data, so 36)?
Microsoft v-yulgu-msft
Microsoft

Re: How do relationships work with ALLEXCEPT in a CALCULATE function?

Hi @Anonymous,

 

To rectify, I'm trying to use ALLEXCEPT in a CALCULATE function so that it will ignore all filters except the filters applied to the HEADCOUNT[Month]

 

Based on my assumption, there are several slicer filters in your report, including month filter, right? For example, if there is a department filter and a month filter, what you want to achieve is to ignore the department filter when computing the DISTINCTCOUNT(HEADCOUNT[Month]), right?

 

If so, please try:

Number of Months = CALCULATE(
			DISTINCTCOUNT(HEADCOUNT[Month]),
			ALL(Headcount[Department]))

Monthly Average Headcount = Count(HEADCOUNT[EMPLOYEE]) / [Number of Months]

If I have something misunderstood, please provide sample data and image to better illustrate your requirement.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: How do relationships work with ALLEXCEPT in a CALCULATE function?

You've described it accurately.

 

However, when I try what you've suggested, it still underreports the # of months in the selected date range.

 

For example, I have 1 individual that is Generation Z in a 6 month span.  However, they left after 1 month.  So, the calculation for # of Months just returns 1 instead of 6 because it is filtering all rows except for Gen Z.  So Average Headcount = 1 instead of 1/6.

 

This is the formula I just tested:

Number of Months = CALCULATE(DISTINCTCOUNT(HEADCOUNT[Month]),All(HEADCOUNT[Generation]))

 

It has the same issue as the ALLEXCEPT formula.  The reason I would prefer to use ALLEXCEPT is because there are other filters in my report that need to be ignored, so ALLEXCEPT should produce a more efficient formula than one which individually specifies every filter that need to be ignored.

 

I modified my calendar table and solved the issue by simply using this formula:

CALCULATE(DISTINCTCOUNT(DateKey[MonthYear]),(DateKey[MonthYear])<>blank())

The only reason I haven't accepted the solution posted previously was because I don't understand how CALCULATE filters work in conjunction with relationships that exist between two tables.  In this instance, DateKey and HEADCOUNT.  I thought that ALLEXCEPT(HEADCOUNT[Month]) would ignore all filters except the one implicitly provided through the datekey relationship.  In reality, it doesn't ignore any filters.  Otherwise, why would I end up with 1 month in the above example?  There is no biderectional relationship that filters the DateKey table...

Microsoft v-yulgu-msft
Microsoft

Re: How do relationships work with ALLEXCEPT in a CALCULATE function?

Hi @Anonymous,

 

Per my understanding, the ALLEXCEPT function removes all context filters in the table rather than ignores the slicer filter. Please see whether this article helpful to you: Filter Data in DAX Formulas

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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