Exclude column of TOP N filter or make constant

[silvias]

Good afternoon,

I have a problem. I need to have in one table the number of episodes per problem, in another the total number of episodes and in another the %. (There are more than 3 rows), so the total is 9508.

That´s right.

The problem is when I filter doing TOP 10 per PROBLEMA_DESC: Since I have now 10 rows instead the 15 that I had in the past, the total number of episodis change to 6690 so percentages change as well.

Num_Episodis = COUNTROWS(SUMMARIZE('Table1,'Table1'[EPISODI],'Table1'[PROBLEMA_DESC],'Table1'[PROBLEMA_CODI]))

NF_Num_Episodis = CALCULATE(COUNTROWS((SUMMARIZE('Table1','Table1'[EPISODI],'Table1'[PROBLEMA_DESC]))), ALLSELECTED('Table1'))

%_Episodis = [Num_Episodis]/[NF_Num_Episodis]

I don´t know how to have a column that doesn´t work with the TOP N filter. I tried with ALLEXCEPT, ALL... but it doesn´t work and I'm not sure if that´s is possible or do a constant.

Thank you in advance.

[Greg_Deckler]

@silvias - If you are doing a filter in the Filter pane, then that pre-filters the data such that DAX calculations lose access to the raw data that is filtered and thus ALL, etc. will not be able to "bring those rows back" so to speak. Are you using a page filter?

[silvias]

Hi @Greg_Deckler , thank you for your answer.

I'm using the TOP N filter in the visualization:

[Greg_Deckler]

@silvias - If it is in the Filter pane, it is going to pre-filter the data such that the DAX does not have access to the filtered data and there is no getting around it. This has come up before in other threads and to the best of my knowledge there is no work-a-round. 

Seems like @Pragati11 was headed down the same path.

[silvias]

@Greg_DecklerThank you, Greg.

I guess I'll have to show all the values without TOP 10 if I want to be shown all of them. Thank you!

[Greg_Deckler · Accepted solution]

@silvias - Yeah, it's a pain and annoying really. ALL is documented as removing all filters but then it doesn't if you use the Filters pane. I wish it wasn't like that.

[Pragati11]

Hi @silvias ,

Just want to check how you are using TOPN filter on this visual?

1. Are you putting a Top N filter on your visual only from FILTER PANE?
2. Are you using a Top N slicer at the top?

Thanks,

Pragati

[silvias]

Hi @Pragati11

thank you for your answer. I'm putting the Top N filter on my visual only from FILTER PANE: