Count the number of repeated orders

[m_aa]

Hi all,

I'm relatively new to Power BI and have some issues with DAX, hope I'll find help here.

What I aim to do is to show the total number of orders in chosen period and how much of them are from returning customers. Both are shown in card visuals. I have no problems with total quantity, however repeated orders...

Here's an example of my data. Basically all fields I need are UserID, Order Number, and Date of Purchase

The result I expect to get is two cards, one of them shows value 25 (the total amount of orders) and 22 (number of repeated orders).

What I managed to do is to define order index number by customer with RANKX function:

rank measure = RANKX(FILTER(ALLSELECTED('OrderTable'),'OrderTable'[USER_ID]=SELECTEDVALUE('OrderTable'[USER_ID])),CALCULATE(SELECTEDVALUE('OrderTable'[ACCOUNT_NUMBER])),,ASC,Dense)

Which leads to this:

The reason I use Account Number as a value instead of date is because there might be few orders in one day which would affect the rank number. I also need a rank to be a measure, not a column, because it should be sensitive to date filter which now works perfectly.

However, I can't use this measure in card filter (don't know why exactly, it's just unavailable when I put the measure in filter field). I tried to make flag measure, something like

flag = IF('rank measure'>1,1,0)

(also tried text values instead of 0 and 1) and use it in filter, but it didn't work either. 

Does anyone know why are these measure are not applicable in filters? Is there a way to make it work? Or do I need completely different approach to this case?

Any help is much appreciated!

Kind regards,

Nastia

[v-cgao-msft · Accepted solution]

Hi @m_aa ,

I created a simple.

Then try these measures.

total number of orders = COUNTROWS('Table')

returning customers = [total number of orders] - COUNTROWS(DISTINCT('Table'[USER_ID]))

The result should be like this and slicer also works fine.

Also, attached the pbix file as the reference.

Best Regards,

Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[m_aa]

Hi @v-cgao-msft ,

this works perfectly, thank you a lot!

The only thing I forgot to mention is that in my data there might be few rows per order (if there were different products in order), so I just added SUMMARIZE function to total orders measure.

Thanks for your help agian!