Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Person | Q1 Att | Q2 Att | Q3 Att | Q4 Att |
A | 10 | 16 | 32 | 32 |
B | 11 | 12 | 12 | 12 |
C | 12 | 13 | 32 | 43 |
D | 13 | 16 | 3 | 23 |
E | 14 | 23 | 43 | 43 |
Solved! Go to Solution.
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! 🤠
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.
@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.
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
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! 🤠
User | Count |
---|---|
73 | |
69 | |
36 | |
26 | |
24 |
User | Count |
---|---|
97 | |
92 | |
54 | |
45 | |
41 |