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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
badger123
Resolver I
Resolver I

Rank with visual filters applied

I have created a rank measure which ranks items in based on size:

 

 

Rank = RANKX(ALLSELECTED('Table1') ,CALCULATE(SUM('Table1'[Size])))

 

 

I also have a measure to calculate the mode of category:

 

 

CatMode = MINX (
TOPN (1,
ADDCOLUMNS (VALUES ( 'Table1'[Category] ),
"Frequency", CALCULATE ( COUNT ( 'Table1'[Category] ) )
),
[Frequency],
0
),
'Table1'[Category]
)

 

 

On my page, I have a slicer for Country. I have a table visual with the items as fields and have applied a visual level filter using CatMode to show items where CatMode contains "Cat A". I also want to apply the Rank measue as a visual level filter to show only the top N items by size of the items that have CatMode Cat A, but this doesn't seem to work. I can add the rank as a field and that works but I cannot seem to apply as a visual level filter in addition to the CatMode filter. 

 

Table 1

ItemSizeRegionCountryCategory
item1121C1Cat A
item1141D1Cat A
item1161E1Cat B

 

Any ideas?? Happy to share more details if needed.

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @badger123 ,

 

Does this measure meet your requirement?

CatMode = MINX (
TOPN (1,
ADDCOLUMNS (ALLEXCEPT( 'Table1', Table1[Item]),
"Frequency", CALCULATE ( COUNT ( 'Table1'[Category] ) )
),
[Frequency],
0
),
'Table1'[Category]
)

1.PNG

 

If not, please provide more description about your desired output. What is the expected result returned by measure [CatMode] in a table visual? I tested with your provided formula and got below result. In that case, why not directly apply filters on column [Category]?

2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yulgu-msft
Employee
Employee

Hi @badger123 ,

 

Does this measure meet your requirement?

CatMode = MINX (
TOPN (1,
ADDCOLUMNS (ALLEXCEPT( 'Table1', Table1[Item]),
"Frequency", CALCULATE ( COUNT ( 'Table1'[Category] ) )
),
[Frequency],
0
),
'Table1'[Category]
)

1.PNG

 

If not, please provide more description about your desired output. What is the expected result returned by measure [CatMode] in a table visual? I tested with your provided formula and got below result. In that case, why not directly apply filters on column [Category]?

2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.