Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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.
Hi @moizsherwani ,
Try this measure:
@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.
I didn't get why you are creating tables for those companies ? Can you explain you target ?
Ricardo
@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
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
79 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |