Direct Query - Calculate function

Question

Hi everyone,&nbsp;&nbsp;Due to the nature of my report, I need to use direct query.&nbsp;&nbsp;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.&nbsp;&nbsp;This is the formula I am trying to write:&nbsp;&nbsp;Current Staff =CALCULATE( COUNTROWS( 'Staff Population' ),&nbsp;&nbsp;&nbsp; FILTER( VALUES( 'Staff Population'[Start Date] ), 'Staff Population'[Start Date] &lt;= MAX( Dates[Date] ) ),&nbsp;&nbsp;&nbsp; FILTER( VALUES( 'Staff Population'[End Date] ), OR( 'Staff Population'[End Date] &gt;= MIN( Dates[Date] ), ISBLANK( 'Staff Population'[End Date] ) ) ) )&nbsp;Any ideas on how I could go around this?&nbsp;&nbsp;Thanks in advance.&nbsp;

icey · Accepted Answer

Hi&nbsp;corange&nbsp;,I create an example. Is this what you want?Current Staff 2 =
CALCULATE (
    COUNTROWS ( StaffPopulation ),
    FILTER (
        StaffPopulation,
        StaffPopulation[EndDate] = BLANK ()
            &amp;&amp; StaffPopulation[StartDate] &lt;= MAX ( Dates[Date] )
    )
)If not, please give me more details without sensitive information.&nbsp;Best Regards,Icey&nbsp;If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.&nbsp;&nbsp;

Forum Discussion

Direct Query - Calculate function

2 Replies

Related content

Direct Lake Model - Calculated Column/Tables

calculate with max() function

SUM function doing wrongly calculation

Direct Query - Time Function - Slow Performance

calculate function