Performance Query about SELECT and FILTER

[Gustavo_Eduardo]

performance query

Which expression is faster performance wise, SELECT() or FILTER(), assuming I only need to get the RowID?

Example:

SELECT(Customers[RowID],USEREMAIL()=[User ID])

FILTER("Customers", USEREMAIL()=[User ID])

In my ignorance I think that Filter since it calls the RowID while Select works a little more looking for any column in the table as a LOOKUP(), however I think I am ignorant about this topic. I appreciate your visions.

[1minManager]

My understaning is that FILTER() and LOOKUP() are based on a SELECT() query anyway.  So the performance difference will be minimal.  Do a search for other ports about performance which go into more detail about this subject.

Simon, 1minManager.com

[Suvrutt_Gurjar · Accepted solution]

I concur with @1minManager 

I think FILTER() is somewhat special representation of SELECT() in that FILTER() works with only key values. Apart from that there is no significant difference in processing for same number of rows.

I was carrying out a test to test the statement above. 🙂

My test on a 37 K rows public dataset table showed that once FILTER() ran faster and at other sync operation,  SELECT() ran faster. So there is really no significant difference.

test : 1 results as below

Test 2 : On the same data source after some time the test 1 was performed is as below

Filter expression used in testing:

FILTER("SWCPlayers", [Team Initials]="FRA")

SELECT () expression used in testing

SELECT( SWCPlayers[ID], [Team Initials]="FRA")

Hope this helps.

[Gustavo_Eduardo]

Thank you @Suvrutt_Gurjar for your time and for trying this out. Thank you so much! Of course it works for me

[Steve]

How are FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() related?