Forum Discussion
RANKX Filter
So, I have a table with different sector names. I have created a ranking measure that ranks them by absolute employment change after the filters on rhs have been applied. See dax below.
RANK = RANKX(ALLSELECTED('SIC 2 lookup'[Sector name]),[SIC 2 absolute change],,DESC)
I then only want to show the top 10 within this table. So I go to filter Rank, where it is less than 10.
However, when I do this in then seems to show only the sectors in the previous table that are in the top 10 without all the filters that have previously been specified, See below.
I would like to show just the top ten in the previous table. Any help would be appreciated, thank you.
Anonymous
This is rather tricky. I'm splitting the answer in several posts since it is quite long. The solution is on the last post.
The issue stems from your using two measures as filters for the visual, with one ([RANK]) somewhat dependent on the rows being shown. To understand what is going on, certainly not trivial, it helps to look at how the visual is generated under the hood . You can gain access to that by copying the query for the visual in the Perfomance analyzer:
// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER (
KEEPFILTERS ( VALUES ( 'SIC 2 lookup'[Sector name] ) ),
NOT ( 'SIC 2 lookup'[Sector name] IN { BLANK () } )
)
VAR __DS0FilterTable2 =
TREATAS ( { "2018" }, 'Date table'[Column] )
VAR __DS0FilterTable3 =
TREATAS ( { "Herefordshire, County of" }, 'Lookup'[Geography] )
VAR __ValueFilterDM0 =
FILTER (
KEEPFILTERS (
SUMMARIZECOLUMNS (
'SIC 2 lookup'[Sector name],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"RANK", 'SIC 2 Employment'[RANK],
"SIC_2_absolute_change", 'SIC 2 Employment'[SIC 2 absolute change],
"SIC2_LQ2", IGNORE ( 'SIC2 LQ'[SIC2 LQ] )
)
),
AND ( [SIC2_LQ2] > 1, [RANK] <= 10 )
)
VAR __DS0Core =
SUMMARIZECOLUMNS (
'SIC 2 lookup'[Sector name],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__ValueFilterDM0,
"RANK", 'SIC 2 Employment'[RANK],
"SIC_2_absolute_change", 'SIC 2 Employment'[SIC 2 absolute change]
)
VAR __DS0PrimaryWindowed =
TOPN ( 501, __DS0Core, [RANK], 1, 'SIC 2 lookup'[Sector name], 1 )
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[RANK],
'SIC 2 lookup'[Sector name]You can see that a table is first generated with all the filters other than those from the measures and after that, both measures are applied as filters. This all happens in __ValueFilterDM0, see the filtering by the measures at the end, with the step (highlighted above): AND ( [SIC2_LQ2] > 1, [RANK] <= 10 )
Then another table is calculated (__DS0Core). This takes __ValueFilterDM0 as base and it calculates the values for the visual once more. It is this that causes the behavior that you are seeing. Here the [RANK] is run again on the base table after it has been filtered.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymous
So, to make it more visual, this is the partial result of __ValueFilterDM0 without the last filtering step ( AND ( [SIC2_LQ2] > 1, [RANK] <= 10 ) )
Then, when that last filtering step ( AND ( [SIC2_LQ2] > 1, [RANK] <= 10 ) ) is added, we get:
the above is the actual, final result of __ValueFilterDM0
And then, finally, __DS0Core is calculated. Note it includes __ValueFilterDM0 in the arguments, so [RANK] is calculated on the table shown above. The ranking numbers thus vary as the ranking is being performed only on the final result of __ValueFilterDM0 and not on all the Sector Name values present at the beginning, when [Rank] was first run while calculating __ValueFilterDM0. The final result (__DS0Core plus some minor aesthetic adjustments) is thus what you got and ere asking about:
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymous
As for how to solve your issue taking the above into account, one option would be to change your [RANK] measure a bit and keep the filters on [RANK] and [SIC LQ] as you had them:
RANK = VAR auxT_ = FILTER ( ALLSELECTED ( 'SIC 2 lookup'[Sector name] ), [SIC2 LQ] > 1 ) RETURN RANKX ( auxT_, [SIC 2 absolute change],, DESC ) //+ RAND () / 100Do note, however, that we are including the visual filter you had on [SIC LQ] as part of the code. This is less flexible/convenient. Given the internal workings that we've seen, it's the solution I've able to come up with (so far). I've also taken out the RAND() part. I didn't know what the logic for it is but in any case it doesn't affect the issue under discussion.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
11 Replies
Anonymous , Try top measure like given example
RANK = RANKX(ALLSELECTED('SIC 2 lookup'[Sector name]),[SIC 2 absolute change],,DESC) +Rand()/100
Top 10 = CALCULATE([SIC 2 absolute change],TOPN(10,all('SIC 2 lookup'[Sector name]),[RANK],DESC),VALUES('SIC 2 lookup'[Sector name]))
This has almost worked. I changed the Top 10 all to all selected like:
Top 10 = CALCULATE([SIC 2 absolute change],TOPN(10,ALLSELECTED('SIC 2 lookup'[Sector name]),[RANK],ASC),VALUES('SIC 2 lookup'[Sector name]))It now shows only the top 10 values and the others below it are blank. When i go to filter topN is not blank. The same problem comes up again as before.
Would just like to show the sector name and absolute change for those that are not blank now.
Anonymous
Just go to the visual filter, and in the filter for the Top10 measure choose to show if it's not blank?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi Anonymous
Can you share the pbix? or a pbix with dummy data that reproduces the issue?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymous
So, to make it more visual, this is the partial result of __ValueFilterDM0 without the last filtering step ( AND ( [SIC2_LQ2] > 1, [RANK] <= 10 ) )
Then, when that last filtering step ( AND ( [SIC2_LQ2] > 1, [RANK] <= 10 ) ) is added, we get:
the above is the actual, final result of __ValueFilterDM0
And then, finally, __DS0Core is calculated. Note it includes __ValueFilterDM0 in the arguments, so [RANK] is calculated on the table shown above. The ranking numbers thus vary as the ranking is being performed only on the final result of __ValueFilterDM0 and not on all the Sector Name values present at the beginning, when [Rank] was first run while calculating __ValueFilterDM0. The final result (__DS0Core plus some minor aesthetic adjustments) is thus what you got and ere asking about:
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymous
As for how to solve your issue taking the above into account, one option would be to change your [RANK] measure a bit and keep the filters on [RANK] and [SIC LQ] as you had them:
RANK = VAR auxT_ = FILTER ( ALLSELECTED ( 'SIC 2 lookup'[Sector name] ), [SIC2 LQ] > 1 ) RETURN RANKX ( auxT_, [SIC 2 absolute change],, DESC ) //+ RAND () / 100Do note, however, that we are including the visual filter you had on [SIC LQ] as part of the code. This is less flexible/convenient. Given the internal workings that we've seen, it's the solution I've able to come up with (so far). I've also taken out the RAND() part. I didn't know what the logic for it is but in any case it doesn't affect the issue under discussion.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
