Forum Discussion

badger123's avatar
badger123
Icon for Resolver I rankResolver I
7 years ago
Solved

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.

Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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]
)

     

    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]?

    Best regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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]
)

     

    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]?

    Best regards,

    Yuliana Gu