Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Adding Serial Numbers and Dynamic Filtering based on Slicer Selection

On Page 1, I have a slicer visual where I used the "Name" column from Table 2
On Page 2, I have a table visual where I used "Name" column from Table 1 and "Percentage" measure from Table 2.
I would like to include an additional field in this table visual that presents the serial number.

Depending on the slicer selection, the table will dynamically adjust to display percentages greater than the selected name

 

Desired table with serial numbers:

sl.no  Name  Percentage
1         d           100
2         b           100
3         i            100
4         a           100
5         h            88
6         g            65
7         e            40
8         f             35
9         c            12

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Anonymous 
    You can create Index column in Power Query

     

     

    Use the sync slicer, and also use the slicer on the second page and hide

     

     

    Create a measure in Table2 and put it into the table visual you created, with the following DAX expression:

     

    Percentage Measure = 
var _currentName = MAX('Table1'[Name])
VAR _currentPercentage = CALCULATE(MAX('Table2'[Percentage]),FILTER(ALL(Table2),'Table2'[Name]=_currentName))
RETURN
IF(MAX('Table2'[Percentage])>=_currentPercentage,MAX('Table2'[Percentage]))

     

    Select slicer in Page 1 and here is my preview:

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Anonymous 
    You can create Index column in Power Query

     

     

    Use the sync slicer, and also use the slicer on the second page and hide

     

     

    Create a measure in Table2 and put it into the table visual you created, with the following DAX expression:

     

    Percentage Measure = 
var _currentName = MAX('Table1'[Name])
VAR _currentPercentage = CALCULATE(MAX('Table2'[Percentage]),FILTER(ALL(Table2),'Table2'[Name]=_currentName))
RETURN
IF(MAX('Table2'[Percentage])>=_currentPercentage,MAX('Table2'[Percentage]))

     

    Select slicer in Page 1 and here is my preview:

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.