Keep page level filter but ignore visual filter

[moizsherwani]

Hello community,

My data is as follows

CompanyDim

CompanyID	CompanyName
1	Company A
2	Company A*
3	Company B
4	Company C

*The Company A being repeated isn't an error, the company name can be repeated

FactTable

Date	CompanyID	Value
1/1/2020	1	200
1/1/2020	2	100
1/1/2020	3	100
1/1/2020	4	50

FactTable is joined with the CompanyDim on the CompanyID column

Problem I am trying to solve. I want to rank to see which company has a the highest average for a month based on the company name. For this I have first created two measures

CompanyAvg = AVERAGE(FactTable[Value])

CompanyAvgRank = RANKX (ALLSELECTED(CompanyDim[CompanyName]),CompanyAvg)

When I put these into a visual table on the canvas the results comes out as expected

CompanyName	CompanyAvg	CompanyAvgRank
Company A	150	1
Company B	100	2
Company C	50	3

I then put a page filter to Filter Out Company A and the result comes out correctly.

CompanyName	CompanyAvg	CompanyAvgRank
Company B	100	1
Company C	50	2

I need to now create two new tables, one for Company B and one for Company C and here is where my problem begins. When I use a visual level filter (for Table1 to keep only Company B and for Table2 to keep only Company C) then as I am using ALLSELECTED it shows as Rank1 for each.

How do I modify the measure so that it keeps the page level filter but ignores the visual filter?*

*I do NOT want to hardcode company names in the measure itself

Thanks,

Moiz

[v-lionel-msft]

Hi @moizsherwani , 

Has your problem been solved? If yes, please consider accept the solution to help the other members find it more quickly.

Best regards,
Lionel Chen

[v-lionel-msft]

Hi @moizsherwani ,

Like this? I change nothing.

I don’t quite understand the reason for your error. Can you give me a similar screenshot?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @moizsherwani ,

Try this measure:

CompanyAvgRank = RANKX (ALL(CompanyDim[CompanyName]); [CompanyAvg])

Ricardo

[moizsherwani]

@camargos88 Thanks and I already tried that but if I do that then it shows me the RANK for Company B to be 2 and Company C to be 3 (the overall rank) even though I want them to be based on whatever is filtered on the page.

[camargos88]

@moizsherwani ,

I didn't get why you are creating tables for those companies ? Can you explain you target ?

Ricardo

[moizsherwani]

@camargos88 I said table to simplify things, they are actually cards for each company (next to the company logo). The report canvas layout is as follows

COMPANY_B_LOGO   CARD

COMPANY_C_LOGO   CARD

The Card shows the Avg and I want the highest average i.e. rank = 1 to be turned Green

[camargos88]

Hi @moizsherwani ,

Once you are gonna filter the visual with the company name, what is the problem to create 1 measure for each company (you just have 2).

Also, like @v-lionel-msft showed here... you can try using another visual like table.

Another solution would be:

RANKX(ALL(CompanyDim[CompanyName]); [CompanyAvg]) - 1

But it would work only for those 3 values...

I don't know how to override only the visual filter, once I use the ALL function it will remove all filters and get all values.

So, it would work with 3 values for names, but if you have more than that it's wrong.

So, the table visual or another visual should work for you.

Ricardo