Forum Discussion
Slicer based on 2 columns
I am trying to create a simple slicer based on 2 columns. All I want is to have a slicer that gives me distinct values from "Open Orders " and "All Open" and filter the measure for revenue based on that slicer.
| Order No | Open Orders | All Open | Revenue |
| 6924659 | $131 | ||
| 6924660 | $231 | ||
| 6924661 | $105 | ||
| 6924662 | Packed | All Open | $75 |
| 6924663 | Packed | All Open | $69 |
| 6924664 | Packed | All Open | $80 |
| 6924665 | Packed | All Open | $43 |
| 6924666 | Packed | All Open | $36 |
| 6924667 | Packed | All Open | $28 |
| 6924668 | Packed | All Open | $71 |
| 6924669 | Packed | All Open | $45 |
| 6924670 | Packed | All Open | $28 |
| 6924671 | Packed | All Open | $41 |
| 6924672 | Packed | All Open | $67 |
| 6924674 | Loaded | All Open | $49 |
| 6924675 | Loaded | All Open | $84 |
| 6924676 | Loaded | All Open | $92 |
| 6924678 | Loaded | All Open | $101 |
| 6924679 | Loaded | All Open | $524 |
| 6924680 | Loaded | All Open | $852 |
| 6924681 | Loaded | All Open | $963 |
| 6924682 | Loaded | All Open | $753 |
| 6924683 | Loaded | All Open | $482 |
| 6924684 | Loaded | All Open | $579 |
| 6924685 | Loaded | All Open | $412 |
| 6924687 | Loaded | All Open | $685 |
This is what I did so far. I created a calculated table based on the follwoing logic
=
DISTINCT (
UNION (
SELECTCOLUMNS ( Orders, "Open Orders", Orders[Open Orders] ),
SELECTCOLUMNS ( Orders, "Open Orders", Orders[All Open] )
)
)
and created a measure to use it as visual filter
Slicer:=
VAR COOrders =
VALUES ( 'CalculatedTable 1'[Open Orders] )
RETURN
IF (
ISFILTERED ( 'CalculatedTable 1'[Open Orders] ),
IF (
MIN ( 'Orders'[Open Orders] ) IN COOrders
|| MIN ( 'Orders'[All Open] ) IN COOrders,
1,
0
),
0
)
Still it is not working.
hi, sgsukumaran
You could try this formula to create a measure for [Revenue]
Measure = var _table=SUMMARIZE(Orders,Orders[Order No],Orders[All Open],Orders[Open Orders],"_Revebue",SUM(Orders[Revenue]),"tag",[Slicer]) return SUMX(FILTER(_table,[tag]=1),[_Revebue])
Result:
Best Regards,
Lin
5 Replies
Hi sgsukumaran ,
Try creating new table like this with this Formula
Slicer = DISTINCT(Union(values(Sheet16[Open Orders]),VALUES(Sheet16[All Open])))
Then create a new measure in your existing table with this formula
Measure = if(min('Sheet16'[All Open])In values(Slicer[new Slicer])|| Min('Sheet16'[Open Orders])In values(Slicer[new Slicer]),1,blank())or
You can also download a Hierarchy Slicer from Marketplace in your Home tab.
Thanks,
Tejaswi
Anonymous - That is exactly what i have except value is passed in variable.
hi, sgsukumaran
I have test on my side, it works well.
I just do some adjustment in the measure
Slicer = VAR COOrders = VALUES ( 'CalculatedTable 1'[Open Orders] ) RETURN IF ( ISFILTERED ( 'CalculatedTable 1'[Open Orders] ), IF ( MIN ( 'Orders'[Open Orders] ) IN COOrders || MIN ( 'Orders'[All Open] ) IN COOrders, 1, 0 ), 1 )Then drag this measure into visual level filter and set is "1". and use new table field as a slicer.
here is my sample pbix file, please try it.
and if you have any other problem, please show it with screenshots.
Best Regards,
Lin
