Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Greetings,
I'm working on trying to write a measure that can do a distinct count of Employee IDs that works over time. In other words, I want to have the Year/Month on my axis, and see a line graph of the number of employees month/by month. Or drop it into another function seleting for the current date and getting a current headcount. Basic headcount stuff.
A few notes about the data:
The model has a Date table. This is joined to a table with some of the employee information in it including the dates.
So I feel like my measure should be something along the lines of:
CALCULATE(
DISTINCTCOUNT(Fact[EmployeeID]),
Date[Date] >= Fact[HireDate] && Fact[EffectiveDate],
Date[Date] <= Fact[TerminationDate] && Fact[ExpirationDate])
There an active join between the Date table and the Hire Date, and inactive joins to the other dates.
My trouble is that I can't seem to get Power BI to let me do the comparison between the dates. It recognizes the Date[Date], but then claims it can't find the others. If I flip it so that it's Fact[Hire Date] <= Date[Date], it finds the Fact[Hire Date] but not the other.
I saw this thread wherein someone suggested using a MAX/MIN function, but then I get the error where it's telling me MAX/MIN is being used in a True/False expression which isn't allowed. What can I do to make this measure work?
Hi @JonV ,
Sorry for my late reply...
Please take a look at this blog for reference about the DAX error.
Since there is no enough data sample , I customized some simple data like this:
Then I merged these two tables based on Index column in order to facilitate filtering conditions:
Measure =
CALCULATE (
DISTINCTCOUNT ( MergeTable[Fact.EmployeeID] ),
FILTER (
'MergeTable',
'MergeTable'[Date] >= 'MergeTable'[Fact.HireDate]
&& 'MergeTable'[Date] >= 'MergeTable'[Fact.EffectiveDate]
&& 'MergeTable'[Date] <= 'MergeTable'[Fact.TerminationDate]
&& 'MergeTable'[Date] <= 'MergeTable'[Fact.ExpirationDate]
)
)
My final result looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @JonV ,
The question in your measure is about the filter part on your calculation.
When you are refering to:
Date[Date] >= Fact[HireDate] && Fact[EffectiveDate]
OR
Date[Date] <= Fact[TerminationDate] && Fact[ExpirationDate]
This is not returning any value since you are searching for Hire data greate than the selected date (this is correct) but then you add AND (&&) Effective date it't not picking up what you need because of the way the relationships are setup.
Without any data is difficult to give you the correct answer, but if you look at the post you refer the MAX and MIN are used within a filter expression that is what I think is missing here.
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |