Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a piece of code in DAX that "works" perfectly. However if I add an extra filter inito the code, then it goes a bit pear shaped. For the life of me I ave tried to fix this and failed miserably. Any suggestions welcome 🙂
The code below. This works perfect - counting staff at month end each month. BUT, only if no slicers are active to filter "Business Area".
EmployeesAtEndOfMonth =
IF(
ISFILTERED('staffmaster'[Business Area]),
CALCULATE(
DISTINCTCOUNT('staffmaster'[Unique ID]),
FILTER(
ALL('staffmaster'),
'staffmaster'[Business Area] IN VALUES ('STAFFMASTER'[Business Area]) &&
('staffmaster'[End Date] >= MAX('Date'[End of Month]) || ISBLANK('staffmaster'[End Date])) &&
'staffmaster'[Start Date] <= MAX('Date'[End of Month])
)
),
CALCULATE(
DISTINCTCOUNT('staffmaster'[Unique ID]),
FILTER(
ALL('staffmaster'),
('staffmaster'[End Date] >= MAX('Date'[End of Month]) || ISBLANK('staffmaster'[End Date])) &&
'staffmaster'[Start Date] <= MAX('Date'[End of Month])
)
)
)
If I select a "Business Area" in a slicer. Lets for example say "HR", then I get some odd behavour. If the month in question did not have someone new starting that month, it doesnt count anything for that month.
Example -
Jan23 - 30 staff (Powerbi counts 30 staff because someone new started month)
Feb 23 - 37 staff (because at least one new start that month)
Mar23 - 0 staff (no new starts this month so powerbi just counts the month as blank)
April 23 - 35 staff (at least one new start)
May23 - 0 staff (no new starts this month)
Jun23 - 36 staff (at least one new start)
So the only correlation between the blanks is that we had no new starts in "HR" in the blank months. I have check against other Business Areas and the correlation seems to hold up. ANy business area where we had a least one new start each month, has no blanks. Any business area where we had no new starts in any month, those months show a blank.
Im no PBI master and I expect the answer is simple enough, but im stumped.
As for data tables the data comes from:
Date Table - marked as date table, called "Date". Has columns for [Date], [End of Month]
Staff table - called Staffmaster. Has columns for [Unique ID], [Start Date], [End Date], [Business Area].
All i want to do is show a number of staff employed on the last day of each month, and be able to filter it by business area.
Solved! Go to Solution.
OK, I have no idea why it was counting that way. And I have rewritten the entire piece to use @amitchandak code from his youtube page. It works perfect under all tests now.
Amits Youtube Linky
OK, I have no idea why it was counting that way. And I have rewritten the entire piece to use @amitchandak code from his youtube page. It works perfect under all tests now.
Amits Youtube Linky
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
70 | |
36 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
24 |