Forum Discussion
using RANKX and allselected together with visual filters
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.
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()))
8 Replies
Hi,
Could you share a dataset and show the expected result.
How do I share the pbix? Can i sent it to your email?
Hi,
Upload to Google Drive and share the download link here.
