Count the number of times data appears in a table, with a filter on

[Anonymous]

I have a large data set which counts the amount of times a person has donated to an organisation. 

For instance it will look like the below 

ID	Amount	Payment Type
1089747	£45.00	Legacy
2937745	£300.00	Pledge
038437	£500.00	Plege
2032896	£300.00	New Donor
2937745	£40.00	Cash Gift

I need a DAX count that counts how many times the ID appears, for instance more than once, twice, three times or more, however, I want to filter this further by New donors and everything else such as cash gift, pledge etc. 

I have the current DAX formula that is counting those that have donated more than once. However, would like a further measure to put these into groups of donated more than once, twice, three times or more. Then filter the measure by who is a new donor or not. 

Count of Donors duplicates =

VAR _CountbyDonor =

ADDCOLUMNS (

SUMMARIZE ( 'Query1', 'Query1'[CnBio_ID] ),

"@Count", CALCULATE ( DISTINCTCOUNT ( 'Query1'[Amount] ) )

)

VAR _GreaterThan1 =

FILTER ( _CountbyDonor, [@Count] > 1 )

VAR _Result =

COUNTROWS ( _GreaterThan1 )

RETURN

_Result

I hope this makes sense.

[v-jayw-msft]

Hi @Anonymous ,

You could create a count measure:

_count = calculate(count(ID),FILTER(ALLESELCTED('Table'),[payment type] = selectedvalue([payment type])&&[ID] = selectedvalue([ID])))

Then create another measure to check who is a new donor or not:

measure = swtich(ture(),
[_count]=1,"New",
[_count]=2,"twice",
[_count]=3,"three times",
[_count]>3,"more then three times"
)

Best Regards,

Jay

[Anonymous]

It's flagging up an error saying " column cannot be used in expression.

[v-jayw-msft]

Hi @Anonymous ,

Which formula show the error?

Both above formulas are measures.

Please share the screenshot.

Best Regards,

Jay

[amitchandak]

@Anonymous , Are you trying to have a frequency as column or bucket, you need following for dynamic segmentation 

example

Bucket/Segment code
measure
Measure = Count(Table[id]) // you measure

new Table // to map frequency with column 
bucket = Generateseries(1,100,1)

new Measure // values have group by  on which we take frequency, Id in your case
Countx(filter(Values(Table[ID]), [Measure ] = max(bucket[Value])), [User])

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

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

[Anonymous]

For the first part you've put //your measure, is it the measure I put in the initial question?