using RANKX and allselected together with visual filters

[jlinhenley]

Hi, I have a problem i cannot figure out.

I have a matrix, with two levels of aggregation and also has a RANKX measure

RANKX(ALLSELECTED(ETOs[Job Number]), [Total ETOs To Date])

It is using ALLSELECTED because in my table, i have another column which I want to filter out via the "visual filters" option on the table. This works fine but i also want to limit the table to show only the 10 ranks (where the rank <= 10). But as soon as I select to also visually filter the rank measure to less than 10, the table is empty, which I understand happens as ALLSELECTED becomes affected but its also being used to calculate the rank.

But knowing this issue, I have not found a way to have this combination work, can someone please help?

Also, I tried using the top N visual filter, but this does not work on all levels of the aggegation (for EG, it will only ever show the top 10 rows, even when i expand the categories. I want to have the top 10 show PER level of aggregation.

[Ashish_Mathur]

Hi,

Could you share a dataset and show the expected result.

[jlinhenley]

How do I share the pbix? Can i sent it to your email?

[Ashish_Mathur]

Hi,

Upload to Google Drive and share the download link here.

[jlinhenley]

Hi, can you download this link

pbix

[Ashish_Mathur]

Hi,

I don't think i have solved it but you may try this.  Remove the condition from the Job Last stage filter.  Revise your measure to

=RANKX(CALCULATETABLE(ALL(ETOs[JobNo]),FILTER(VALUES(ETOs[JobNo]),[Job Last Stage]<>"Fixing")), [Total ETOs To Date])

[jlinhenley]

 Thanks for taking a look. I feel there must be a way to generate the rankings without using ALLSELECTED.

In any case, this seemed like it should be a lot easier..

[Ashish_Mathur]

You are welcome.  I have not used ALLSELECTED().  However, even though i have explicitly asked the formula to filter out the Fixing rows, they are appearing in the end result.  Sorry but out of ideas now.

[jlinhenley · Accepted solution]

I ended up solving this issue. What i had to do was create a dimension table on the activities (but dont create a relationship back to the job activities). Then used this as the filter to apply for rank and using the values option in RANKX to actifically blank out the unselected stages. Works well, but computationally quite hard on the cpu.

RANKX(
	FILTER(
		ALL(ETOs[Job Number]),
		[Job Last Stage] IN VALUES('Activities Table'[ActivityDesc])),
	[Total ETOs To Date],
	IF([Job Last Stage] IN VALUES('Activities Table'[ActivityDesc]), [Total ETOs To Date], BLANK()))