Make first few rows unresponsive to slicer Power BI

Question

I have a data which looks like below:

  Brands  Sales Category Index
  Brand1    588        A     1
  Brand2    846        A     2
  Brand3    827        A     3
  Brand4    951        A     4
  Brand5    673        B     5
  Brand6    637        B     6
  Brand7    575        B     7
  Brand8    995        B     8
  Btand9    737        C     9
 Brand10    661        C    10
 Brand11    729        C    11
 Brand12    789        C    12
 Brand13    836        C    13

Problem statement :

I am trying to put Category as a slicer. However I want the rows for Category A to be present in the table view irrespective of the slicer which is selected.

Example: Lets say if Category B is selected in slicer , in this case the table should return all rows until Rank 8.

Below is an example of the desired output when category C is selected:

As you can see, the visual table has both Category A and Category C.

Similarly when both B and C are selected, I should be able to display all the categories (A,B and C).

What tried: I was thinking if we can use a conditional DAX which return 1 for selected values in slicers and mark rest as 0, I could use that as a visual filter and filter out 0. I tried various combinations of Filter with in Filters and SELECTCOLUMN but it did not work. Even the below measure returns all the rows instead of Selected values|| category="A"

test1 = CALCULATE(MIN('Table'[Index]),FILTER(ALLEXCEPT('Table','Table'[Brands]),'Table'[Category]=SELECTEDVALUE('Table'[Category]) || 'Table'[Category]="A"))

I also tried something like:

test = var cat = min('Table'[Category]) return IF(cat = SELECTEDVALUE('Table'[Category])||cat="A",1,0)

But this gives all as 1 , doesnot give 0 for rows which does not match the condition (note I have blocked the slicer interaction here)

Any help would be highly appreciated.

mkRabbani mkRabbani · Accepted Answer · 2020-09-30T16:33:17

First, you need to separate your slicer table as keeping the value in the same table you can not achieve the requirement. You can create custom table with this below code-

considering your base table name sales

Lets create the custom table category list

No relation can be there between table sales and category list

category list = 
SELECTCOLUMNS(
    sales,
    "Category",sales[Category]
)

Now, create the slicer using new custom table category list and create this below Measure-

is filter = 
if(
    MIN(sales[Category]) = "A",
    1,
    if (
        MIN(sales[Category]) IN VALUES('category list'[Category]),
        1,
        0
    )
)

Here below is a sample output when C selected-

Make first few rows unresponsive to slicer Power BI

1 Answers