VAR and FILTERS combination to make an efficient column calculation

[masplin]

I have a table UserStatusbyMinute with 14mio rows. I need to add a new column that looks up a value from one table, then reuses that value to look up a second value from another table.  I've got it working, but wonder what is the optimum way to write the DAX.

The first lookup is this.  Does it make any difference the order of the FILTER statements as Joined time has very high cardinality and PhoneUserID much lower?

IFERROR(CALCULATE(values(CallParticipants[ConferenceId]),
                   FILTER(
                          CallParticipants,
                          UserStatusByMinute[PhoneUserid]=CallParticipants[PhoneUserId] &&
                          UserStatusByMinute[StatusDate]=CallParticipants[JoinedDate] &&
                          RELATED('Time'[Time])>=CallParticipants[JoinedTime] &&
                          RELATED('Time'[Time])<=CallParticipants[FinishTime]
                         )
),0)

The second lookup then uses this and i can think of 3 ways to do it

1. Use the calcuation above to create a column [CallID] then use

Call Type = 
CALCULATE(VALUES(Calls[Direction and Case Type]),
                FILTER(Calls,
          Calls[CallId]=UserStatusbyMinute[CallID]
))

2. Use the phase 1 calcuation as a VAR in the second calculation

Call Type = 
VAR
CallID=IF(UserStatusByMinute[Status]<>"OnCall",BLANK(),
IFERROR(CALCULATE(values(CallParticipants[ConferenceId]),
                   FILTER(
                          CallParticipants,
                          UserStatusByMinute[PhoneUserid]=CallParticipants[PhoneUserId] &&
                          UserStatusByMinute[StatusDate]=CallParticipants[JoinedDate] &&
                          RELATED('Time'[Time])>=CallParticipants[JoinedTime] &&
                          RELATED('Time'[Time])<=CallParticipants[FinishTime]
                         )
),0))
RETURN
CALCULATE(VALUES(Calls[Direction and Case Type]),
                FILTER(Calls,
          Calls[CallId]=[CallID]
)

3. Write it in one go

Call Type = 
CALCULATE(VALUES(Calls[Direction and Case Type]),
                FILTER(Calls,
          Calls[CallId]= 
IF(UserStatusByMinute[Status]<>"OnCall",BLANK(),
IFERROR(CALCULATE(values(CallParticipants[ConferenceId]),
                   FILTER(
                          CallParticipants,
                          UserStatusByMinute[PhoneUserid]=CallParticipants[PhoneUserId] &&
                          UserStatusByMinute[StatusDate]=CallParticipants[JoinedDate] &&
                          RELATED('Time'[Time])>=CallParticipants[JoinedTime] &&
                          RELATED('Time'[Time])<=CallParticipants[FinishTime]
                         )
),0))))

Version 2. failed as ran out of memory, as presumably it is calculating the whole 14mio row CAllID table in memory and then passing that into the 2nd calcaution? The third version is doing the whole calculation row by row and seems faster.  Is the 1st version any differnet to the 2nd version except pre calculating the number in a column instead of holding it in memory i.e. breaks the calculation in two?

I've bene a bit slack in the past thinking about efficiency so need to improve my understanding of  best practice. Thanks for any advice

Mike

[masplin]

I couldnt get DAX studio working so resorted to the old stopwatch!!!

The answer is solution 1 is fastest at 2:58 i.e. cretae the intermediate vlaue as a calculated column. Using VAR was 2nd @ 3:12 and writting one big long formula was slowest at 3:34.  

so using VAR is faster than not, but actually using an extra calculated column rather thna VAR is even better!! Not what i was expecting.

I do wish Microsoft people would not marked thnigs as solved when they aren't as stil ldon't see how to do this exercise in DAX studio

[kentyler]

I think its important to realize that calculated columns are calculated when you create them and the results are stored in the model. Every time you run the report they are not recalculated.

Measures are calculated every time you run the report. So a calculated column would not impose a speed penalty after it was calculated and stored, any more than any other data in the table.

[kentyler]

Have you tried running these in Dax Studio. It will give you execution times you can compare.

[masplin]

There is a tool I know nothing about. Taking the simplest version can you tell me what syntax I need to use to run this in DAX studio please?

Call Type = 
CALCULATE(VALUES(Calls[Direction and Case Type]),
                FILTER(Calls,
          Calls[CallId]=UserStatusbyMinute[CallID]
))

[v-lid-msft · Accepted solution]

Hi @masplin ,

Follow this blog to install the dax studio

then connect the opened pbix file in dax studio

open server timing, run the query

or you can use the Performance Analyzer in the Power BI Desktop, please refer to this document:

https://docs.microsoft.com/en-us/power-bi/desktop-performance-analyzer

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[masplin]

Is this query XCOLUMN adding a column called "Test" to the table UserStatusByMinute?  Presumably I put my actual code in the last bit like this?  This retruns an error "Object reference not set to an instanceof an object"?

XCOLUMNS(UserStatusByMinute,"Test",IF(UserStatusByMinute[Status]<>"OnCall",BLANK(),
IFERROR( CALCULATE(values(CallParticipants[ConferenceId]),
                   FILTER(
                          CallParticipants,
                          UserStatusByMinute[PhoneUserid]=CallParticipants[PhoneUserId] &&
                          UserStatusByMinute[StatusDate]=CallParticipants[JoinedDate] &&
                          RELATED('Time'[Time])>=CallParticipants[JoinedTime] &&
                          RELATED('Time'[Time])<=CallParticipants[FinishTime]
                         )
),9)))

[masplin]

so that query just tests the calcaution time of the column [CallID]

Perfect thanks