cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Abhaykumar
Microsoft
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
Phil_Seamark
Microsoft
Microsoft

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
Phil_Seamark
Microsoft
Microsoft

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.