Frequency of counts

[Abhaykumar]

I have a table of event data:

Machine	FilterableColumn_1	FilterableColumn_2
m1	x1	y2
m1	x1	y1
m2	x1	y1
m3	x2	y3
m1	x1	y1
m2	x2	y2
m1	x3	y3
m4	x1	y1
m1	x2	y2
m3	x1	y1
m1	x2	y1

I would get event count per machine:

Machine	EventCount
m1	6
m2	2
m3	2
m4	1

Next, I need to get the frequency of event counts (final table):

EventCount	Frequency
1	1
2	2
6	1

This is what would be plotted with EventCount as X-Axis and Frequency as Y-Axis.

I need to have the final table respond to filters on the event table. For example, if only x1 and x2 are selected from the events table, my final table would change to reflect the new set of rows. 

I am trying to get this done by using SUMMARIZE, but it creates a table that does not respond to the filters. 

Is there a way to make SUMMARIZE respond to filter context? Any other way of doing this?

Thanks.

[Phil_Seamark · Accepted solution]

Hi @Abhaykumar

I think I have something that might work for you.

First, create a numbers calculated table using the following code.

Table = GENERATESERIES(1,100,1)

Then add a calculated measure to this table as follows

Measure =  COUNTROWS(
	 FILTER(
		 SUMMARIZE(
			 'Table1',
			 Table1[Machine],
			 "c",COUNTROWS('Table1')
			 ),
		[c]=MAX('Table'[Value])
		)
	)

This should produce a measure that repects filters AND allows you to add to the axis of a scatter chart.

You can download a PBIX file with this working here

https://1drv.ms/u/s!AtDlC2rep7a-n2ZoduzVau30mAoA