Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Background:
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?
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
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
Why don't you count the months from your calendar table and not the data table?
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |