cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft Abhaykumar
Microsoft

Frequency of counts

I have a table of event data:

 

MachineFilterableColumn_1FilterableColumn_2
m1x1y2
m1x1y1
m2x1y1
m3x2y3
m1x1y1
m2x2y2
m1x3y3
m4x1y1
m1x2y2
m3x1y1
m1x2y1

 

I would get event count per machine:

 

MachineEventCount
m16
m22
m32
m41

 

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

 

EventCountFrequency
11
22
61

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Phil_Seamark
Microsoft

Re: Frequency of counts

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

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

1 REPLY 1
Microsoft Phil_Seamark
Microsoft

Re: Frequency of counts

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

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors