Count of Orders per User ID within a time frame

[Anonymous]

Hi,

I have a table (Orders) that contains information about orders: User_id, Order_date and Order_price.

This table is linked to a date.table. What I would like to have is a measure that counts how many users made 1,2,3,4,5,.... orders. Basically a histogram of count of orders per user.  I need to be able to filter this measure later on based on specific dates.

Maybe I don't have to realize this in a measure but in a seperate table or column? Not really sure how to proceed here. Any help is appreciated. I have created this measure using SUMMARIZE in a seperate table, but this is of course no longer sensitive to filtering by order date.

Best

[V-lianl-msft]

Hi @Anonymous ,

Is this problem sloved?
If not, please let me know.

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

[V-lianl-msft]

Hi @Anonymous ,

According to your description, you use the date in the date table as the slicer and create a relationship between two tables.

You can try the DAX:

count_id =
CALCULATE (
    COUNT ( Orders[User_id] ),
    FILTER (
        ALLEXCEPT ( Orders, Orders[User_id] ),
        Orders[Order_date] IN VALUES ( 'Date'[Date] )
    )
)

See the pbix for details.

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

[Anonymous]

Hi @V-lianl-msft ,

thanks for your input. Unfortunately the code doesn't produce the output I require. What it does produce is a histogram that shows for every individual user_id the number of orders that individual has placed.

What I need however is a histogram that shows the number (count) of users that have place 1, 2, 3, ... orders in the specified time period. Would you have an idea how to do that?

Cheers,

Lukas

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Do you want to use histogram to show the number of orders per user in each time period?

Please refer to the pbix

If I understand it wrong, please provide some sample data and the results you expect.

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