topic Count the number of times data appears in a table, with a filter on in DAX Commands and Tips

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

Anonymous — Wed, 23 Mar 2022 14:35:55 GMT

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.

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

amitchandak — Wed, 23 Mar 2022 16:45:21 GMT

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

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

Anonymous — Fri, 01 Apr 2022 13:39:15 GMT

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

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

Anonymous — Mon, 04 Apr 2022 09:11:04 GMT

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

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

Anonymous — Mon, 04 Apr 2022 09:11:54 GMT

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

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

Anonymous — Wed, 06 Apr 2022 07:47:24 GMT

Hi Anonymous ,

Which formula show the error?

Both above formulas are measures.

Please share the screenshot.

Best Regards,

Jay