locked
DAX Query Pagination Logic RRS feed

  • General discussion

  • Hi,

    I am working on a DAX query engine that an application will use to generate DAX dynamically and execute it against a tabular model.  One of the requirements is for the query to support pagination, so if the user only want records 20 through 40 then the DAX will return only rows 20 through 40 after the correct sorting is applied.

    I created an example DAX query that works, however it does not perform well when executed on a large table.  It takes about 20 seconds to return something that was 0 seconds before.  Here my test query:

    evaluate 
    topn(
    	@EndRow
    	,values(Table1)
    	,COUNTAX(
    		filter(
    			Table1
    			,Table1[Field1]
    			<=
    			earlier(
    				Table1[Field1]
    				,1
    			)
    			&&
    			Table1[Field2]
    			<=
    			earlier(
    				Table1[Field2]
    				,1
    			)
    		)
    		,Table1[Field2]
    	 )
    	,1
    )
    order by rankx(
    			Table1,
    			COUNTAX(
    				filter(
    					Table1
    					,Table1[Field1]
    					<=
    					earlier(
    						Table1[Field1]
    						,1
    					)
    					&&
    					Table1[Field2]
    					<=
    					earlier(
    						Table1[Field2]
    						,1
    					)
    				)
    				,Table1[Field2]
    			 )
    		,,1,Dense
    		)
    start at @StartRow

    The way the pagination works is the "Start At" value is where the @StartRow goes and the "TOPN" value is where the @EndRow goes. 

    The part I am struggling with is creating an efficient row number column with the right sorting.  If I could do that efficiently, then I think I would have a performance DAX query with pagination.

    Thanks!


    Tuesday, June 4, 2013 11:06 AM

All replies