Count & Group by for column with filter across month

[thampton]

i have a table with clientid / salesorder# / orderdate. I would like to have a measure to count how many clients have more than 2 or more orders total. So im thinking this would need to summarize, then do a filter, but not sure how that syntax would work. 

For example, below is an example table. I would like a measure to show 1 because only 1 client has had 2 or more orders in this time frame. 

I would also like this to cross month. So If i have this connected to my date table, i would like to add in the measure and have it count by month. So if i choose september, it should show 1, if i choose october it would recount, etc..

Client           Order ID               Date

a                   1563                    9-2-2020

a                   1234                   9-2-2020

a                   2345                   9-6-2020

b                  5678                    9-7-2020

[v-deddai1-msft · Accepted solution]

Hi @thampton ,

You can create a date table and create one to many relationship between date table and fact table.

Then you can use the following measure:

Measure = var a = SUMMARIZE('Table','Table'[Client ],"orders total",COUNT('Table'[Order ID  ])) return COUNTX(FILTER(a,[orders total]>=2),'Table'[Client ])

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Eb6mZMKcBrlHgZGezC...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@thampton , Try a measure like

sumx(filter(summarize(Table, Table[Client], "_1", count(Table[Order ID])), [_1]>2),[Client])