Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-yulgu-msft
Employee
Employee

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

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...

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.

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



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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.