How to use a Slicer for counting distinct values?

[golandriel]

Hi all,

I want to use a slicer to filter my data set on how many entries I have for one ID. In my example I want to know every tree that has exactly one bush in a perimeter of 100m. In my table every tree has a distance to the next bush and the bushes are numbered sorted by distance (see the table below). I want to set my slicer that the result should be 1 (only the tree with the ID 3). If I want to know how many trees have exactly 2 bushes in a perimeter of 100m, the result should also be 1 (only the tree with the ID 2)

Right now when I set my slicer (Number of Bushes) on 1 it returns all three trees because every tree has at least 1 Bush in its perimeter. How can I fix this?

Thank you!

Tree ID	Bush ID	Distance in m	Number of Bushes
1	300	30	1
1	301	50	2
1	302	70	3
2	400	15	1
2	401	20	2
3	500	10	1

[v-zhengdxu-msft]

Hi @golandriel 

Thanks for the reply from @iceparrot , please allow me to provide another insight:

First of all, I create a new table:

Table 2 = VALUES('Table'[Number of Bushes])

Then add a measure:

MEASURE =
VAR _Slicer =
    MAX ( 'Table 2'[Number of Bushes] )
VAR _currenttreeID =
    MAX ( 'Table'[Tree ID] )
VAR _countofBush =
    CALCULATE (
        MAX ( 'Table'[Number of Bushes] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Tree ID] = _currenttreeID )
    )
RETURN
    IF ( _countofBush = _Slicer, _countofBush )

The report can be like:

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[golandriel]

Thank you for your help @v-zhengdxu-msft!

It's not quite the result I am looking for. If I change the distance to 60m Tree ID 1 has also two bushes in a perimeter of 60m and should therefore appear in the list. But it won't appear in the list because the (max) measure of that tree ID is 3.

Do you have an idea how I can fix that?

Best regards

Frederik

[iceparrot]

Hey @golandriel , 

according to your description you want to filter by the amount of Bushes in a specified distance, right?

If your answer is yes, you could just use a second date slicer and use the field Distance in m as an additional filter. 
I used the demo data provided by you and this is how it looks like with two date slicers:

[golandriel]

Hi @iceparrot,

thanks for your reply. Not quite! I already have a slicer for the distance. If you set your slicer to 100m and your slicer for the number of bushes to 1 it would still return all 3 tree IDs. In my case I need a slicer that only returns the ID for trees with exactly 1 bush in 100m (Tree ID 3).