Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need a measure to calculate the employees that were actives in some period and there respective "Cost Center" (department), this measure can't "summarize" the total when no filter data is applied in the slicers, should show the actual (last) value from the last month in the context.
I have one table of all Employess of the company, and in this table l have HireDate and FiredDate as well, and another table where I have the "history" changes of the employee, where I will have the start / end of the period that the employee was in determined Cost Center and his Rate, for example:
From this Hist Table, I created a new Table (DAX) to explode the data between Start / End date, as bellow:
With those data, I need one measure that will count if the employee is active in determined month/year and where we was allocated (Cost Center).
I have something like the DAX bellow, but the results are not what I expected:
Headcount =
DISTINCTCOUNT(Worker_Hist[Employee ID])
Headcount_3 =
CALCULATE( COUNTROWS( Worker_Hist ) ;
FILTER ( VALUES( Worker_Hist[Start Date] ); Worker_Hist[Start Date] <= MAX ( Calendar[Date] ) );
FILTER ( VALUES( Worker_Hist[End Date] ); OR ( Worker_Hist[End Date] > MAX ( Calendar[Date] );
ISBLANK( Worker_Hist[End Date] ) ) ) )
Filter Year = 2019 / Month = All month
The rules is:
I hope that someone could help me with that.
Thanks in advanced.
Lúcio Andrade
Solved! Go to Solution.
I think the measure will work better if you check all conditions in one FILTER statement like this:
Headcount_4 =
CALCULATE (
[HeadCount]
FILTER (
Worker_Hist;
Worker_Hist[Start Date] <= MAX ( Calendar[Date] )
&& (
Worker_Hist[End Date] > MAX ( Calendar[Date] )
|| ISBLANK ( Worker_Hist[End Date] )
)
)
)
Then of course you can add more logic to the filter as well.
Dear @Anonymous and @Anonymous thanks for your post and comments.
Actually the measures do not achived what I need. I'm preparing a PB sample to share here, just making sure that confidential data will not be shared.
Thanks.
Lúcio
Try something like this:
Headcount =
var __maxVisibleDate = MAX ( Calendar[Date] )
var __minVisibleDate = MIN ( Calendar[Date] )
return
CALCULATE (
DISTINCTCOUNT ( Worker_Hist[Employee ID] ),
KEEPFILTERS ( Worker_Hist[Start Date] <= __minVisibleDate ),
KEEPFILTERS ( Worker_Hist[End Date] > __maxVisibleDate )
) +
CALCULATE(
DISTINCTCOUNT ( Worker_Hist[Employee ID] ),
KEEPFILTERS ( Worker_Hist[Start Date] <= __minVisibleDate ),
KEEPFILTERS ( ISBLANK ( Worker_Hist[End Date] ) )
)
I can't do any better because I don't fully understand the definition of the measure.
Best
D
I think the measure will work better if you check all conditions in one FILTER statement like this:
Headcount_4 =
CALCULATE (
[HeadCount]
FILTER (
Worker_Hist;
Worker_Hist[Start Date] <= MAX ( Calendar[Date] )
&& (
Worker_Hist[End Date] > MAX ( Calendar[Date] )
|| ISBLANK ( Worker_Hist[End Date] )
)
)
)
Then of course you can add more logic to the filter as well.
@AnonymousAs always I think it depends. But I have worked on some quite large tables before and then the FILTER function can really kill the performance (since it iterates the whole table). But yes, in this case the oppisite might be true since the filter in on VALUES([column]) and not on the whole table. But it's hard to tell without seeing the complete dataset and test the different ways to write the DAX.
But the reason I suggested to have one more complex filter in this case was that there might be logic that has dependencies on row level that cant be separated into many simple filters. So it had nothing to do with performance.
I might have been a little bit to fast writing an answer in this case, the requirements are a little bit hard to follow... But I think it can at least give a hint. But more logic (that I didn't understand) perhaps needs to be added.
Well, @Anonymous, sorry but you're not right. CALCULATE has been optimized to work with filters of the type:
table[column] = value
and nothing can be faster than this. There is some internal "magic" and tricks in the engine that make this true.
You can read about it on www.sqlbi.com or in The Book by The Italians (The Ultimate Guide to DAX).
Best
D
@AnonymousYes, I'm aware of the CALCULATE magic on simple filters. But as far I have have understood it doesn't apply when you have filters with OR, MAX and other that was the case here. Then you cant use the simple CALCULATE([Measure], [Column]=xxx) format, you have to specify an implicit FILTER that is a lot slower. I think one of "the Italians" taught me this on my very first DAX course, but that was almost 10 years ago, so it might have been improved in newer versions? However, it is of cause better if you can have separate filters on columns instead of the whole table. But in some cases that could be hard because of the logic.
Just to drive the point home...
The formula in the first post contains, for instance, such a line:
FILTER ( VALUES( Worker_Hist[Start Date] ); Worker_Hist[Start Date] <= MAX ( Calendar[Date] ) )
This is nothing but
KEEPFILTERS( Worker_Hist[Start Date] <= value )
which does happen to be a simple filter for CALCULATE.
Apart from table[column] = value, the simple filters also contain the relational operators <, <=, >, >=.
Best
D
Ok , @Anonymous thank's for information. Happy to learn something new 🙂 The reason I thought this was still not optimized is the error message you get when trying to write the measure in the "simple format" (or in KEEPFILTERS):
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
27 | |
23 | |
14 | |
11 |
User | Count |
---|---|
77 | |
63 | |
44 | |
17 | |
12 |