Get all items of a specificized value

[MichaelRensing]

I have a dataset of items that have a grouping. A sample of this data is in the following table:

Item	Groupng ID
1A	Grp - 1 -24
1B	Grp - 1 -24
1B	Grp - 1 -24
2A	Grp - 1 -24
1A	Grp - 2 -24
2A	Grp - 2 -24
4A	Grp - 2 -24
18A	Grp - 2 -24
5A	Grp - 3 - 24
5C	Grp - 3 - 24
5D	Grp - 3 - 24
X5	Grp - 3 - 24
5B	Grp - 4 - 24
5D	Grp - 4 - 24
5D	Grp - 4 - 24
X5	Grp - 4 - 24
X5	Grp - 4 - 24
1B	Grp - 5 - 24
5D	Grp - 5 - 24
5C	Grp - 5 - 24
5B	Grp - 5 - 24
1C	Grp - 5 - 24
X5	Grp - 5 - 24

What I am looking to do is select an item via a slicer and then get back all the items in all the groups that the selected items appears in.  For Example if I select X5, I would get the all the items from the groups Grp - 3 -24, Grp - 4 -24, and Grp - 5 -24. I am looking to count the items, find percentages, etc with the returned data.

How would I go about getting this?

[Anonymous · Accepted solution]

Hello,hnguy71 and Ashish_Mathur ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@MichaelRensing .I am glad to help you.

Like this?
By implementing the effect of the slicer's reverse selection of data you can achieve what you want: generate a selection table based on the multiple (single values) selected in the slicer. Display them in the table. Also calculate the corresponding quantities (total or grouped counts (based on Grouping ID))

Control the filtering results through measure, instead of using slicer directly.

I hope you find my tests useful.
this is my test data.

1. Re-create a table that does not build relationships, but is only used to pass slicer options

The slicer here only passes values without any filtering effect (so no need to create a relationship)

Create a measure that acts as a reverse filter (supports slicer multi-selection).

Set visuals to be filtered by measure and only display data that satisfies [M_choose]=1

Calculate the total number of filtered data

M_CountALLSelected = CALCULATE(COUNT('TestData'[Item]),FILTER(ALL(TestData),[M_choose]=1))

Calculate the number of filtered data grouped by [Gourping ID].

M_CountByGroup = 
VAR _groupID=MAX('TestData'[Groupng ID])
RETURN
CALCULATE(COUNT('TestData'[Item]),FILTER(ALL(TestData),[M_choose]=1 &&'TestData'[Groupng ID]=_groupID))

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

I have answered a similar question in the attached file.

Hope this helps.

[MichaelRensing]

@hnguy71 
This would only give me the X5 Items.  I would like to get ALL items in the Grouping ID.  So It would give me back all of Grp - 3 -24, Grp - 4 - 24, and Grp - 5 -24.  Any group that X5 shows in, not just the X5s

[hnguy71]

Hi @MichaelRensing 

Then you would need an additional step to remove the filter context from the item selected. Create a measure:

GroupCount = CALCULATE(COUNTA(YOUR_TABLE[Groupng ID]), REMOVEFILTERS(YOUR_TABLE[Item]))

[MichaelRensing]

@hnguy71 I tried this and am still not getting what I am expecting. With none selected, the count is 23, selecting 4A, I was expecting a result of 4, but still seeing 23.

And the measuer for Group Count is 

Group Count = CALCULATE(COUNTA('Sheet1'[Groupng ID]), REMOVEFILTERS(Sheet1[Item]))

[hnguy71]

Hi @MichaelRensing ,

So in this case, you want to add back some filter criteria. To get the corrected "totals", you would need to use SUMX against your total.

This should work:

Group.Count = 
VAR _Tbl = SUMMARIZE(YOUR_TABLE, YOUR_TABLE[Groupng ID], "@Group", CALCULATE(COUNTA(YOUR_TABLE[Groupng ID]), REMOVEFILTERS(YOUR_TABLE[Item])) )
RETURN
SUMX(_Tbl, [@Group])

This should be your expected results:

[hnguy71]

Hi @MichaelRensing,
Using a basic slicer would the the trick for you.

Then you would use a basic measure for your count, similarly to this:

Item.Count = COUNTROWS(YOUR_TABLE)