Plotting graph based on measure with slicer

[Irisohyama6]

Good day,

My data:

Location	Employee	YN
Wellington	Jon	Y
Wellington	Kevin	N
Wellington	Oisin	Y
Wellington	Isaac	Y
Wellington	Pat	Y
New York	Jon	N
New York	Kevin	Y
New York	Oisin	Y
New York	Isaac	N
Cairo	Jon	Y
Cairo	Kevin	Y
Cairo	Oisin	N
Cairo	Isaac	Y
Cairo	Pat	N
Cairo	Deb	Y
Tokyo	Isaac	Y
Tokyo	Pat	N
Tokyo	Deb	Y

From this data, I created a measure that count "Y" for each employee:

Count Yes = COUNTROWS(FILTER('Table','Table'[YN]="Y"))

I put them in a table visual:

From this table I can see how many occurence of "Count Yes". Looks something like this:

From this, I want to plot a graph like this:

For this graph, I want to able to use the slicer where the columns count will change depends on which 'Location'(s) I picked.

Also, I need this chart to be dynamic as I will add data in the future. The max "Count Yes" is 3 now, but it may be 4 for the next data I add in.

My attempt:

Because the x-axis depends on the maximun count of the [Count Yes] measure, I created a measure to count the maximum:

Count Yes max per Employee = 
MAXX(KEEPFILTERS(VALUES('Table'[Employee])), CALCULATE([Count Yes]))

From here, I created another table to generate the range of x-values for the column chart:

Table 2 = GENERATESERIES(1,[Count Yes max per Employee],1)

From here I got stuck because I don't know how to fill another column with this:

I understand there are multiple ways of doing this. I am open to any solutions. Here is my file. 

Thank you all in advance.

[amitchandak]

@Irisohyama6 , a new measure and values from Table 2 should be used in visual

Sumx(filter(VALUES('Table'[Employee]), [Count Yes] = max(Table2[Value])),[Count Yes] )

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[Irisohyama6]

Hi @amitchandak ,

Thanks for the swift reply. 

I created a measure given by you. Somehow I didn't get the result.

[amitchandak · Accepted solution]

@Irisohyama6 , You need employee count

Emp Count = Countx(filter(VALUES('Table'[Employee]), [Count Yes] = max(Table2[Value])),[Employee] )