The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi,
I have a below table,
ID | Level | Sale |
AA | Level1 | 200 |
BB | Level2 | 300 |
CC | Level3 | 400 |
DD | Level1 | 250 |
EE | Level4 | 500 |
FF | Level3 | 450 |
GG | Level2 | 350 |
HH | Level1 | 270 |
In filter, when I select ID=BB, then the below result should show:
ID | Level | Sale |
BB | Level2 | 300 |
GG | Level2 | 350 |
and again in filter, when I select ID=HH, then the below result should show
ID | Level | Sale |
HH | Level1 | 270 |
AA | Level1 | 200 |
DD | Level1 | 250 |
Please let me know how to achive this?
Solved! Go to Solution.
Here's how I would do it - firstly i would duplicate the ID and Level into a seperate table (you can do this in Power Query or DAX) and then add a slicer for Duplicate[ID]. I can then grab the related level using SELECTEDVALUE(Duplicate[Level]) and use that in a filter for the original table. So something like:
CALCULATE(SUM(OriginalTable[Sale]), OriginalTable[Level] = SELECTEDVALUE(Duplicate[Level]))
If you have multiple selections, then you can try and replace OriginalTable[Level] = filteredLevel with OriginalTable[Level] in VALUES(Duplicate[Leve]).
I haven't tested this code, so you'll need to do some tweaking to get it to work with your data.
Hi @chiriazu
You can create a new table, and put the column of new table to the slicer
Table 2 = SUMMARIZE('Table',[ID])
Then create a measure in table
Measure = var _relatedlevel=MAXX(FILTER(ALL('Table'),[ID]=SELECTEDVALUE('Table 2'[ID])),[Level])
var _relateddata=MAX('Table'[Level])
return IF(ISFILTERED('Table 2'[ID])=FALSE(),1,IF(_relatedlevel=_relateddata,1,0))
Then put the measure in visual filter
Output:
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @chiriazu
You can create a new table, and put the column of new table to the slicer
Table 2 = SUMMARIZE('Table',[ID])
Then create a measure in table
Measure = var _relatedlevel=MAXX(FILTER(ALL('Table'),[ID]=SELECTEDVALUE('Table 2'[ID])),[Level])
var _relateddata=MAX('Table'[Level])
return IF(ISFILTERED('Table 2'[ID])=FALSE(),1,IF(_relatedlevel=_relateddata,1,0))
Then put the measure in visual filter
Output:
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous Thanks much, this also helps.
If we select ID = AA then the below result should show:
ID | Level | Sale |
HH | Level1 | 270 |
DD | Level1 | 250 |
AA | Level1 | 200 |
Here's how I would do it - firstly i would duplicate the ID and Level into a seperate table (you can do this in Power Query or DAX) and then add a slicer for Duplicate[ID]. I can then grab the related level using SELECTEDVALUE(Duplicate[Level]) and use that in a filter for the original table. So something like:
CALCULATE(SUM(OriginalTable[Sale]), OriginalTable[Level] = SELECTEDVALUE(Duplicate[Level]))
If you have multiple selections, then you can try and replace OriginalTable[Level] = filteredLevel with OriginalTable[Level] in VALUES(Duplicate[Leve]).
I haven't tested this code, so you'll need to do some tweaking to get it to work with your data.
Thank Vicky,
I have rechecked this and now its working. Thanks
Thanks for quick response.
But it didn't work.
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
8 | |
8 |