Measure that calculates the # of times an ID appears in a list, binned

[mmace1]

I have a payment table, that shows a long list of payments, and the ID (cust_ven) of the recipient of the payment.  So some cust_ven IDs show up multiple times in the table, some only once.

I'd like to display say:

# of cust_vens that show up in the list 1 time = x

# of cust-Vens that show up in the list 2-5 times = y

etc.

In a clustered column chart. 

That would be very easy to do if grouping the payment table by cust_ven at the query step.  Group by Cust_ven, then add a SWITCH statement that bins them, and we're done. 

But...I can'd do that, because I need to be able to filter by date range.  So really it'd be,

<during the dates you selected>

# of cust-Vens that show up 1 time = x

# of cust_vens that show up in the list 2-5 times = y

etc.

Kind of like (and this fails), but:

Measure 2 = calculate(count(Payment[Cust_ven]),count(Payment[Cust_ven]=1))

?

[Phil_Seamark · Accepted solution]

Hi @mmace1

Here is one way to do it, if you are happy with a calculated measure per grouping.  The first line is the same for both measures..  Only the filter condtion in the RETURN statement is different.  This should respect date filters.  I hope you get the idea.

Count of 1 Ven = 
VAR MyGrouping = GROUPBY('Table2','Table2'[cust_vens],"Count of Rows" , COUNTX(CURRENTGROUP(),1))
RETURN COUNTROWS(filter(MyGrouping,[Count of Rows]=1))

and

Count of 2-5 Ven = 
VAR MyGrouping = GROUPBY('Table2','Table2'[cust_vens],"Count of Rows" , COUNTX(CURRENTGROUP(),1))
RETURN COUNTROWS(filter(MyGrouping,[Count of Rows] > 1 && [Count of Rows] <= 5))

[mmace1]

Man, I don't know how that works - but that works.  Thanks!

Edit:  Blast from the past - OK I mostly understand how that works now.