Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
moizsherwani
Continued Contributor
Continued Contributor

Keep page level filter but ignore visual filter

Hello community,

 

My data is as follows

 

CompanyDim

CompanyIDCompanyName
1Company A
2Company A*
3Company B
4Company C

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

 

FactTable

DateCompanyIDValue
1/1/20201200
1/1/20202100
1/1/20203100
1/1/2020450

 

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

 

CompanyNameCompanyAvgCompanyAvgRank
Company A1501
Company B1002
Company C503

 

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

 

CompanyNameCompanyAvgCompanyAvgRank
Company B1001
Company C502

 

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

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
7 REPLIES 7
v-lionel-msft
Community Support
Community Support

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
Community Support
Community Support

Hi @moizsherwani ,

 

Like this? I change nothing.

a1.PNG

 

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
Community Champion
Community Champion

Hi @moizsherwani ,

 

Try this measure:

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@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.

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

@moizsherwani ,

 

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

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@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

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.