Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi everyone,
Due to the nature of my report, I need to use direct query.
I found out that there is limitations especially when it comes to DAX formula. I am trying to build up something using the calculate function but it won't work.
This is the formula I am trying to write:
Current Staff =
CALCULATE( COUNTROWS( 'Staff Population' ),
FILTER( VALUES( 'Staff Population'[Start Date] ), 'Staff Population'[Start Date] <= MAX( Dates[Date] ) ),
FILTER( VALUES( 'Staff Population'[End Date] ), OR( 'Staff Population'[End Date] >= MIN( Dates[Date] ), ISBLANK( 'Staff Population'[End Date] ) ) ) )
Any ideas on how I could go around this?
Thanks in advance.
Solved! Go to Solution.
Hi @corange ,
I create an example. Is this what you want?
Current Staff 2 =
CALCULATE (
COUNTROWS ( StaffPopulation ),
FILTER (
StaffPopulation,
StaffPopulation[EndDate] = BLANK ()
&& StaffPopulation[StartDate] <= MAX ( Dates[Date] )
)
)
If not, please give me more details without sensitive information.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @corange ,
I create an example. Is this what you want?
Current Staff 2 =
CALCULATE (
COUNTROWS ( StaffPopulation ),
FILTER (
StaffPopulation,
StaffPopulation[EndDate] = BLANK ()
&& StaffPopulation[StartDate] <= MAX ( Dates[Date] )
)
)
If not, please give me more details without sensitive information.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for your help on this one!! It did work for me.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
28 | |
26 |
User | Count |
---|---|
97 | |
88 | |
59 | |
43 | |
40 |