Forum Discussion
Post ProdigyHow can i put this question in a formula ?
Im struggeling with the folowing question.
I have an orderlist with a couple of thousand orders and some customers placed more then 1 order, some even 10 or 20 orders.
What I need is a formula so that i can use a Slicer to bundel/combine the customers which placed:
1 order
Between 2 and 5 orders.
Between 6 and 15 orders etc.
I think this will take a calculated colum which should check how many times a customer is in the list and depending on that number place them in 1 of the groups for example 'Between 6 and 15 orders'.
Hopefully you understand what i'm trying to say.
How would it go in the project table? This formula is evaluated at a row context. For each row, it counts the number of orders associated with that account. If you put it in the project table you're asking it to count other rows in the same table based on what? The row context is orders in that table, not accounts. If you want a count of orders per account, you need to use the account table as your starting place.
As for the error message I think those need to be double quotes, not single quotes. Your regional settings require different punctuation from mine so I've just been copying yours assuming it was correct, but your use of single quotes is probably incorrect.
Number of Orders = VAR ordercount = CALCULATE( COUNTA(Project[ID]) ) RETURN SWITCH( TRUE(); ordercount = 1; "1 order"; ordercount > 1 && ordercount <= 5; "2 to 5 orders"; ordercount > 5 && ordercount <= 20; "6 to 20 orders"; "More than 20 orders" )
30 Replies
- ankitpatira
Community Champion
RvdHeijden In that case just go to query editor -> under Add Column tab -> Conditional Column as below.
- RvdHeijden
That doesnt work because my Customers have iD's with such as 0630000000ex1siAAA so if i use a conditial column it gives me other options then the ones you gave me.
U can choose 'Greater then or equal to' but im only getting 'equals', 'Does not equal', Ends with
- ankitpatira
RvdHeijden You need to use column that contains actual Order numbers not customer ids. In my screenshot I've shown order number column. Also make sure order number column is declared of data type Whole Number and you will see same options as in the screenshot.
What you're looking for is right here. You need to read this through and do a little study to understand what's going on here.
http://www.daxpatterns.com/parameter-table/
- RvdHeijden
Anonymous
Ive been checking different sites for the last couple of days but with no result because i just can wrap my head around the dax formulas just yet.
That is why i asked my questions on this forum hoping that someone can help me.
Ive partially read the site but i got lost halfway through
RvdHeijden I don't understand why you keep talking about the customer ID column. You said you wanted to use the number of orders. ankitpatira advised you to write a conditional statement based on a column that shows the number of orders, not the customer ID. Do you not have a column for number of orders? It's difficult to give advice if we don't know how your data is structured or what this table contains.
