Forum Discussion

patilpoonam21's avatar
patilpoonam21
Helper I
2 years ago
Solved

Create a Range Slicer for multiple columns

Hi,

 

I need to show the Range slicer as below for the multiple columns in the Table visual. In the below table for Q1 Att, Q2 Att, Q3 Att and Q4 Att.
Is it possible? I am using Power BI Desktop 2019 version.

Thanks in advance!

Table:

PersonQ1 AttQ2 AttQ3 AttQ4 Att
A10163232
B11121212
C12133243
D1316323
E14234343

 

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    patilpoonam21 

    output : 

     

     

    create 4 measures 

    att q1 measure ,  att q2 measure,  att q3 measure, att q4 measure 

    all of them same code with one difference :  the calculation 

     

    check below : 

    Q1 ATT

    q1 att measure = 
var min_value = MIN(slicer[Value])
var max_value = MAX(slicer[Value])
var dataousrce = 
FILTER(
ADDCOLUMNS(
    VALUES('Table'[Person]),
    "@x" ,CALCULATE(SUM('Table'[Q1 Att]))
)
,[@x] >= min_value && [@x] <= max_value
)

var res = 
CALCULATE(
    SUM('Table'[Q1 Att]),
dataousrce
)
return res

     

    Q2 ATT

    Q2 att measure = 
var min_value = MIN(slicer[Value])
var max_value = MAX(slicer[Value])
var dataousrce = 
FILTER(
ADDCOLUMNS(
    VALUES('Table'[Person]),
    "@x" ,CALCULATE(SUM('Table'[Q2 Att]))
)
,[@x] >= min_value && [@x] <= max_value
)

var res = 
CALCULATE(
    SUM('Table'[Q2 Att]),
dataousrce
)
return res

     

     

     

    Q3 ATT

    Q3 att measure = 
var min_value = MIN(slicer[Value])
var max_value = MAX(slicer[Value])
var dataousrce = 
FILTER(
ADDCOLUMNS(
    VALUES('Table'[Person]),
    "@x" ,CALCULATE(SUM('Table'[Q3 Att]))
)
,[@x] >= min_value && [@x] <= max_value
)

var res = 
CALCULATE(
    SUM('Table'[Q3 Att]),
dataousrce
)
return res

     

     

    ATT Q4

    Q4 att measure = 
var min_value = MIN(slicer[Value])
var max_value = MAX(slicer[Value])
var dataousrce = 
FILTER(
ADDCOLUMNS(
    VALUES('Table'[Person]),
    "@x" ,CALCULATE(SUM('Table'[Q4 Att]))
)
,[@x] >= min_value && [@x] <= max_value
)

var res = 
CALCULATE(
    SUM('Table'[Q4 Att]),
dataousrce
)
return res

     

     

     

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    patilpoonam21 Hard to tell exactly what you want given the information provided by looks like you could use a Complex Selector: The Complex Selector - Microsoft Fabric Community

     

    If not, Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • patilpoonam21's avatar
      patilpoonam21
      Helper I
      2 years ago

      Hi Greg_Deckler ,

       

      Thank you for your reply!

      If the range is selected between 1 to 10 in the slicer, I need all 4 columns to display the values that are between 1 to 20. 

       

      Thanks,

      Poonam

      • Daniel29195's avatar
        Daniel29195
        Community Champion
        2 years ago

        patilpoonam21 

        output : 

         

         

        create 4 measures 

        att q1 measure ,  att q2 measure,  att q3 measure, att q4 measure 

        all of them same code with one difference :  the calculation 

         

        check below : 

        Q1 ATT

        q1 att measure = 
var min_value = MIN(slicer[Value])
var max_value = MAX(slicer[Value])
var dataousrce = 
FILTER(
ADDCOLUMNS(
    VALUES('Table'[Person]),
    "@x" ,CALCULATE(SUM('Table'[Q1 Att]))
)
,[@x] >= min_value && [@x] <= max_value
)

var res = 
CALCULATE(
    SUM('Table'[Q1 Att]),
dataousrce
)
return res

         

        Q2 ATT

        Q2 att measure = 
var min_value = MIN(slicer[Value])
var max_value = MAX(slicer[Value])
var dataousrce = 
FILTER(
ADDCOLUMNS(
    VALUES('Table'[Person]),
    "@x" ,CALCULATE(SUM('Table'[Q2 Att]))
)
,[@x] >= min_value && [@x] <= max_value
)

var res = 
CALCULATE(
    SUM('Table'[Q2 Att]),
dataousrce
)
return res

         

         

         

        Q3 ATT

        Q3 att measure = 
var min_value = MIN(slicer[Value])
var max_value = MAX(slicer[Value])
var dataousrce = 
FILTER(
ADDCOLUMNS(
    VALUES('Table'[Person]),
    "@x" ,CALCULATE(SUM('Table'[Q3 Att]))
)
,[@x] >= min_value && [@x] <= max_value
)

var res = 
CALCULATE(
    SUM('Table'[Q3 Att]),
dataousrce
)
return res

         

         

        ATT Q4

        Q4 att measure = 
var min_value = MIN(slicer[Value])
var max_value = MAX(slicer[Value])
var dataousrce = 
FILTER(
ADDCOLUMNS(
    VALUES('Table'[Person]),
    "@x" ,CALCULATE(SUM('Table'[Q4 Att]))
)
,[@x] >= min_value && [@x] <= max_value
)

var res = 
CALCULATE(
    SUM('Table'[Q4 Att]),
dataousrce
)
return res

         

         

         

         

         

         

        If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !✅
        It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    If Q1 ATT etc. are individual columns in your dataset, then you may want to use the "Unpivot Other Columns" feature to flatten the table.  You will then be able to use the slicer easily.