Solved: Adding a sort by column in a Tabular SSAS breaks m...

dedmedved · ‎11-21-2023

After I add Main Order as the order by column for Main Description, my top 5 query pulls back all results.

Obviously somehow the new column in the automatically generated DAx is behind this, but I can't worrk out how, or how to fix it.

Because of the design of the system I can't eally change the model

Heres the new DAX - (generated by PowerBI)

// DAX Query
DEFINE
	MEASURE 'Select Top N'[Products Total Quantity Filter] = 
		(/* USER DAX BEGIN */

VAR direction = IF(HASONEVALUE(TopOrBottom[TopOrBottom]), values(TopOrBottom[TopOrBottom]),"Top")
return IF(direction = "Top",[Top Products Total Quantity Filter],[Bottom Products Total Quantity Filter])
/* USER DAX END */)

	MEASURE 'Select Top N'[Top Products Total Quantity Filter] = 
		(/* USER DAX BEGIN */

VAR dimRank =
    VALUES ( 'Product Lookup'[Main Description] )
VAR rankValue = 'Select Top N'[Selected N]
RETURN
    CALCULATE (
        [Total Quantity],
        FILTER (
            dimRank,
            RANKX (
                ALL ( 'Product Lookup'[Main Description] ),
                [Total Quantity],
                ,
                DESC,Dense
            ) <= rankValue
        )
    )
/* USER DAX END */)

	MEASURE 'Select Top N'[Selected N] = 
		(/* USER DAX BEGIN */
VAR Selection = IF ( HASONEVALUE ( 'Select Top N'[Top N] ), VALUES ('Select Top N'[Top N] ) )
return Selection
/* USER DAX END */)

	MEASURE 'Select Top N'[Bottom Products Total Quantity Filter] = 
		(/* USER DAX BEGIN */

VAR dimRank =
    VALUES ( 'Product Lookup'[Main Description] )
VAR rankValue = 'Select Top N'[Selected N]
RETURN
    CALCULATE (
        [Total Quantity],
        FILTER (
            dimRank,
            RANKX (
                filter( all ( 'Product Lookup'[Main Description]  ),[Total Quantity]<>0),                
                // ALL ( 'Product Lookup'[Main Description] ),
                [Total Quantity],
                ,
                ASC,Dense
            ) <= rankValue
        )
    )
/* USER DAX END */)

	VAR __DS0FilterTable = 
		FILTER(
			KEEPFILTERS(VALUES('Product Lookup'[Main Description])),
			'Product Lookup'[Main Description] <> "UNKNOWN"
		)

	VAR __DS0FilterTable2 = 
		FILTER(
			KEEPFILTERS(VALUES('TopOrBottom'[TopOrBottom])),
			'TopOrBottom'[TopOrBottom] = "Bottom"
		)

	VAR __DS0FilterTable3 = 
		FILTER(KEEPFILTERS(VALUES('Select Top N'[Top N])), 'Select Top N'[Top N] = 5)

	VAR __DS0FilterTable4 = 
		FILTER(
			KEEPFILTERS(VALUES('Date lookup'[calendar_year])),
			'Date lookup'[calendar_year] = 2022
		)

	VAR __ValueFilterI0 = 
		FILTER(
			KEEPFILTERS(
				SUMMARIZECOLUMNS(
					'Date lookup'[Calendar Year Month],
					'Date lookup'[date_key],
					'Product Lookup'[Main Description],
					'Product Lookup'[Main Order],
					__DS0FilterTable,
					__DS0FilterTable2,
					__DS0FilterTable3,
					__DS0FilterTable4,
					"Products Total Quantity Filter", 'Select Top N'[Products Total Quantity Filter]
				)
			),
			[Products Total Quantity Filter] <> 0
		)

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			'Date lookup'[Calendar Year Month],
			'Date lookup'[date_key],
			'Product Lookup'[Main Description],
			'Product Lookup'[Main Order],
			__DS0FilterTable,
			__DS0FilterTable2,
			__DS0FilterTable3,
			__DS0FilterTable4,
			__ValueFilterI0,
			"Products Total Quantity Filter", 'Select Top N'[Products Total Quantity Filter]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			201,
			SUMMARIZE(__DS0Core, 'Date lookup'[Calendar Year Month], 'Date lookup'[date_key]),
			'Date lookup'[date_key],
			1,
			'Date lookup'[Calendar Year Month],
			1
		)

	VAR __DS0SecondaryBase = 
		SUMMARIZE(__DS0Core, 'Product Lookup'[Main Description], 'Product Lookup'[Main Order])

	VAR __DS0Secondary = 
		TOPN(
			62,
			__DS0SecondaryBase,
			'Product Lookup'[Main Order],
			1,
			'Product Lookup'[Main Description],
			1
		)

	VAR __DS0BodyLimited = 
		NATURALLEFTOUTERJOIN(
			__DS0PrimaryWindowed,
			SUBSTITUTEWITHINDEX(
				__DS0Core,
				"ColumnIndex",
				__DS0Secondary,
				'Product Lookup'[Main Order],
				ASC,
				'Product Lookup'[Main Description],
				ASC
			)
		)

EVALUATE
	__DS0Secondary

ORDER BY
	'Product Lookup'[Main Order], 'Product Lookup'[Main Description]

EVALUATE
	__DS0BodyLimited

ORDER BY
	'Date lookup'[date_key], 'Date lookup'[Calendar Year Month], [ColumnIndex]

Here's the orginal and working DAX

// DAX Query
DEFINE
	MEASURE 'Select Top N'[Products Total Quantity Filter] = 
		(/* USER DAX BEGIN */

VAR direction = IF(HASONEVALUE(TopOrBottom[TopOrBottom]), values(TopOrBottom[TopOrBottom]),"Top")
return IF(direction = "Top",[Top Products Total Quantity Filter],[Bottom Products Total Quantity Filter])
/* USER DAX END */)

	MEASURE 'Select Top N'[Top Products Total Quantity Filter] = 
		(/* USER DAX BEGIN */

VAR dimRank =
    VALUES ( 'Product Lookup'[Main Description] )
VAR rankValue = 'Select Top N'[Selected N]
RETURN
    CALCULATE (
        [Total Quantity],
        FILTER (
            dimRank,
            RANKX (
                ALL ( 'Product Lookup'[Main Description] ),
                [Total Quantity],
                ,
                DESC,Dense
            ) <= rankValue
        )
    )
/* USER DAX END */)

	MEASURE 'Select Top N'[Selected N] = 
		(/* USER DAX BEGIN */
VAR Selection = IF ( HASONEVALUE ( 'Select Top N'[Top N] ), VALUES ('Select Top N'[Top N] ) )
return Selection
/* USER DAX END */)

	MEASURE 'Select Top N'[Bottom Products Total Quantity Filter] = 
		(/* USER DAX BEGIN */

VAR dimRank =
    VALUES ( 'Product Lookup'[Main Description] )
VAR rankValue = 'Select Top N'[Selected N]
RETURN
    CALCULATE (
        [Total Quantity],
        FILTER (
            dimRank,
            RANKX (
                filter( all ( 'Product Lookup'[Main Description]  ),[Total Quantity]<>0),                
                // ALL ( 'Product Lookup'[Main Description] ),
                [Total Quantity],
                ,
                ASC,Dense
            ) <= rankValue
        )
    )
/* USER DAX END */)

	VAR __DS0FilterTable = 
		FILTER(
			KEEPFILTERS(VALUES('Product Lookup'[Main Description])),
			'Product Lookup'[Main Description] <> "UNKNOWN"
		)

	VAR __DS0FilterTable2 = 
		FILTER(
			KEEPFILTERS(VALUES('TopOrBottom'[TopOrBottom])),
			'TopOrBottom'[TopOrBottom] = "Top"
		)

	VAR __DS0FilterTable3 = 
		FILTER(KEEPFILTERS(VALUES('Select Top N'[Top N])), 'Select Top N'[Top N]  = 5)

	VAR __DS0FilterTable4 = 
		FILTER(
			KEEPFILTERS(VALUES('Date lookup'[calendar_year])),
			'Date lookup'[calendar_year] = 2022
		)

	VAR __ValueFilterI0 = 
		FILTER(
			KEEPFILTERS(
				SUMMARIZECOLUMNS(
					'Date lookup'[Calendar Year Month],
					'Date lookup'[date_key],
					'Product Lookup'[Main Description],
					__DS0FilterTable,
					__DS0FilterTable2,
					__DS0FilterTable3,
					__DS0FilterTable4,
					"Products Total Quantity Filter", 'Select Top N'[Products Total Quantity Filter]
				)
			),
			[Products Total Quantity Filter] <> 0
		)

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			'Date lookup'[Calendar Year Month],
			'Date lookup'[date_key],
			'Product Lookup'[Main Description],
			__DS0FilterTable,
			__DS0FilterTable2,
			__DS0FilterTable3,
			__DS0FilterTable4,
			__ValueFilterI0,
			"Products Total Quantity Filter", 'Select Top N'[Products Total Quantity Filter]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			201,
			SUMMARIZE(__DS0Core, 'Date lookup'[Calendar Year Month], 'Date lookup'[date_key]),
			'Date lookup'[date_key],
			1,
			'Date lookup'[Calendar Year Month],
			1
		)

	VAR __DS0SecondaryBase = 
		SUMMARIZE(__DS0Core, 'Product Lookup'[Main Description])

	VAR __DS0Secondary = 
		TOPN(62, __DS0SecondaryBase, 'Product Lookup'[Main Description], 1)

	VAR __DS0BodyLimited = 
		NATURALLEFTOUTERJOIN(
			__DS0PrimaryWindowed,
			SUBSTITUTEWITHINDEX(
				__DS0Core,
				"ColumnIndex",
				__DS0Secondary,
				'Product Lookup'[Main Description],
				ASC
			)
		)

EVALUATE
	__DS0Secondary

ORDER BY
	'Product Lookup'[Main Description]

EVALUATE
	__DS0BodyLimited

ORDER BY
	'Date lookup'[date_key], 'Date lookup'[Calendar Year Month], [ColumnIndex]

lbendlin · ‎11-22-2023

"The Italians" discussed this at length. If you sort a column by another column then both need to be mentioned in the TOPN or RANKX filters, in REMOVEFILTERS etc etc.

View solution in original post

dedmedved · ‎11-23-2023

Thanks,

I think I can see where it needs fixing.

I'll try to find the relevant article/video.

Ciao

lbendlin · ‎11-22-2023

"The Italians" discussed this at length. If you sort a column by another column then both need to be mentioned in the TOPN or RANKX filters, in REMOVEFILTERS etc etc.

Adding a sort by column in a Tabular SSAS breaks my topn visual in my PowerBI report

Helpful resources

Power BI Monthly Update - November 2025

Fabric Data Days

FabCon Atlanta 2026

How to Get Your Question Answered Quickly

FabCon is coming to Atlanta