COUNTX running very slow - anyway to improve performance?

[dancarr22]

Hello,

We are calculating XIRR in PBI.  There are several associated measures.

Some of the measures run slow - example of one below.  Any idea how to speed up or why this is occurring?  Relatively speaking, there are not many records in the table - about 500k.  And we use DirectQuery.

Slow running measure is:

XIRR_BeginRMV_Date_IsBefore = VAR StartDate = [XIRR_BeginDate]

VAR HowMany  = CALCULATE(COUNTX(XIRR_Lookup,XIRR_Lookup[TransactionDate]),XIRR_Lookup[MappingName]="Reported Market Value", XIRR_Lookup[TransactionDate]<StartDate)

RETURN IF(COALESCE(HowMany,0)=0,"AFTER","BEFORE")

And mainly the slow part is VAR HowMany.

Thanks,

Dan 

[dancarr22]

Thanks so much @tamerj1 - that worked!

I actually have one (maybe 2) new related questions which I will post separately.

Sincerely appreciate your assistance!
Dan

[tamerj1 · Accepted solution]

Hi @dancarr22 

please try

XIRR_BeginRMV_Date_IsBefore =
VAR StartDate = [XIRR_BeginDate]
VAR Check =
ISEMPTY (
FILTER (
XIRR_Lookup,
XIRR_Lookup[MappingName] = "Reported Market Value"
&& XIRR_Lookup[TransactionDate] < StartDate
)
)
RETURN
IF ( Check, "AFTER", "BEFORE" )