Direct Query - Calculate function

[corange]

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. 

[Icey · Accepted 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.

[corange]

Thank you so much for your help on this one!! It did work for me.