Show data in Category based on filter on item

[Anonymous]

Hello Experts,

I have data table where data is presented as Category -> Item and details of individual item.

See below sample.

Category	Item	Quantity	Price	Other Columns
Fruits	Banana	1	9	Some other data
Fruits	Apple	8	6	Some other data
Fruits	Mango	1	0	Some other data
Fruits	Orange	0	8	Some other data
Cereals	Wheat	1	3	Some other data
Cereals	Oats	9	3	Some other data
Cereals	Rice	3	3	Some other data
Dairy	Cow Milk 1%	7	7	Some other data
Dairy	Cow Milk 2%	7	2	Some other data
Dairy	Buffalow Milk 1%	4	7	Some other data
Dairy	Buffalow Milk 2%	0	8	Some other data
Dairy	Buffalow Milk Full Fat	2	4	Some other data
Dairy	Cheese	2	1	Some other data

I want to create a Slicer or Text Filter that shows details of all items in a category when an item in that category is searched.

Example:

If I use slicer or text filter to search for "Cheese" which is in Item Column I should see the table showing everything in dairy like below:

Category	Item	Quantity	Price	Other Columns
Dairy	Cow Milk 1%	7	7	Some other data
Dairy	Cow Milk 2%	7	2	Some other data
Dairy	Buffalow Milk 1%	4	7	Some other data
Dairy	Buffalow Milk 2%	0	8	Some other data
Dairy	Buffalow Milk Full Fat	2	4	Some other data
Dairy	Cheese	2	1	Some other data

[amitchandak · Accepted solution]

@Anonymous , You need create an independent item, category table(  do not join with Table)

ItemCat =

Summarize(Table, Table[Item], Table[Category])

Use item slicer from this table

and use following measure example for all calculated meausre or use it as visual level filter

measure =

var _tab = summarize(Allselected(Itemcat), Itemcat[Cat])

return

calculate(Sum(Table[Value]), filter(Table, Table[Cat] in _tab))

[Anonymous]

Thanks Amit,

Editer Reply:
Okay, I applied the measure as filter on the original table in the view. I had to do 2 things -
1. Add a column as Value and make all of them "1".
2. Apply a filter "Value is 1" in the table.

Original Message Below:

Using Sum(Table[Value]) in the output function returns singular count, but not rows with each entry. Maybe I didn't fully understand how to do this!