The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I am trying to create a slicer that groups together various items in one column from an exisiting table.
For example, if my existing table contains a column "Food" like so:
Food |
Strawberries |
Carrots |
Cucumbers |
Cake |
Banana |
Orange |
I would like to create a slicer with 3 options, Fruit, Vegetables and Dessert, where Fruit filters my dashboard to rows containing "Strawberries", "Banana" and "Orange, Vegetables filters to rows containing "Carrots" and "Cucumbers", and Desserts filters to rows containing "Cake".
Thanks
Solved! Go to Solution.
You could add a conditional column in Power query for a new column
OR
create a new table to use as the slicer using:
Slicer =
VAR Dessert =
ADDCOLUMNS (
CALCULATETABLE ( VALUES ( OriginalTable[Food] ), OriginalTable[Food] = "Cake" ),
"Type", "Dessert"
)
VAR Vegetables =
ADDCOLUMNS (
CALCULATETABLE (
VALUES ( OriginalTable[Food] ),
OriginalTable[Food] IN { "Cucumbers", "Carrots" }
),
"Type", "Vegetables"
)
VAR Fruit =
ADDCOLUMNS (
EXCEPT (
VALUES ( OriginalTable[Food] ),
UNION (
CALCULATETABLE ( VALUES ( OriginalTable[Food] ), OriginalTable[Food] = "Cake" ),
CALCULATETABLE (
VALUES ( OriginalTable[Food] ),
OriginalTable[Food] IN { "Cucumbers", "Carrots" }
)
)
),
"Type", "Fruit"
)
RETURN
UNION ( Dessert, Vegetables, Fruit )
Or Create a copy of the table (Modeling -> New Table) and type Slicer = 'OriginalTable'
Then
Then create a 1:* relationship between the food fields in both tables.
Proud to be a Super User!
Paul on Linkedin.
You could add a conditional column in Power query for a new column
OR
create a new table to use as the slicer using:
Slicer =
VAR Dessert =
ADDCOLUMNS (
CALCULATETABLE ( VALUES ( OriginalTable[Food] ), OriginalTable[Food] = "Cake" ),
"Type", "Dessert"
)
VAR Vegetables =
ADDCOLUMNS (
CALCULATETABLE (
VALUES ( OriginalTable[Food] ),
OriginalTable[Food] IN { "Cucumbers", "Carrots" }
),
"Type", "Vegetables"
)
VAR Fruit =
ADDCOLUMNS (
EXCEPT (
VALUES ( OriginalTable[Food] ),
UNION (
CALCULATETABLE ( VALUES ( OriginalTable[Food] ), OriginalTable[Food] = "Cake" ),
CALCULATETABLE (
VALUES ( OriginalTable[Food] ),
OriginalTable[Food] IN { "Cucumbers", "Carrots" }
)
)
),
"Type", "Fruit"
)
RETURN
UNION ( Dessert, Vegetables, Fruit )
Or Create a copy of the table (Modeling -> New Table) and type Slicer = 'OriginalTable'
Then
Then create a 1:* relationship between the food fields in both tables.
Proud to be a Super User!
Paul on Linkedin.
@Anonymous , Create a new column and use that in the slicer
Switch( True() ,
[food] in {"Strawberries", "Banana" ,"Orange"} , "Fruit",
[food] in {"Strawberries", "Carrots" ,"Cucumbers"} , "Vegetables ",
"Desserts"
)
User | Count |
---|---|
78 | |
74 | |
43 | |
32 | |
28 |
User | Count |
---|---|
104 | |
95 | |
51 | |
50 | |
46 |