Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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" )
Proud to be a Super User!
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/
@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.
Proud to be a Super User!
Your absolutely right so ill try to clarify my problem better.
I have 2 tabels that matter to this problem
1. Account
2. Project
1. In the table of Account is a list of all the orders which have been placed witch their own (order)ID
2. In the table of Project are alle the clients with their own (Client) ID
Our client ID's are combinations of Numbers and Letters, there is nothing i can do about that.
What im trying to do is check how many orders a client has placed because i want to use a slicer to select clients who have placed:
1 order
> 2 and < 5 orders
> 6 and < 20 orders
etc
Maybe i need a new table which calculates (per client) the number of orders they placed but im not sure
or maybe i need to use RELATED to get data from the 'Project' and 'Account' table in order to get the Clients name
im not sure (and a newb at this) that i AM sure of 🙂
@RvdHeijden is there a relationship between these two tables already? I'm guessing from your description that Project has unique values for ClientID, so each ClientID appears only once in that table, and that each order (each OrderID) in Account also has that ClientID referenced, so you have a 1 to many relationship from Project[ClientID] to Account[ClientID]. Is that accurate?
If so, you can create a column like this in the Project table:
Number of Orders = VAR ordercount = CALCULATE( COUNTA( RELATED(Account[OrderID]) ) ) 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" )
Proud to be a Super User!
You are right there is a relationship between both tabeles.
I copied your formula to the Project table but if gives a syntax error 'The Syntax for "1 order" is incorrect.
The same error will probably occur in the other values i think
Number of Orders = VAR ordercount = CALCULATE(
COUNTA(
RELATED(Account[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'
I already want to thank you for your help, i wouldn't have been able to this on my own
I think this is a language issue. You need to replace the rest of my commas with semicolons. Also it looks like you're missing the close parenthesis from the end of the SWITCH statement.
Number of Orders = VAR ordercount = CALCULATE( COUNTA( RELATED(Account[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' )
Proud to be a Super User!
Now i get the error that 'The COUNTA function only accepts a column reference as an argument'
Number of Orders = VAR ordercount = CALCULATE( COUNTA( RELATED(Account[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' )
ive got the feeling we are close to the solution
Oh, you know what, you probably don't even need RELATED because it's already wrapped in a CALCULATE.
Number of Orders = VAR ordercount = CALCULATE( COUNTA(Account[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' )
Proud to be a Super User!
The formula is now error free however it gives an all the rows the same value which is '1 order'.
I know there are many projects from the same client so there should be more values in this column then just the '1 order'.
Somehow the formula doesn't check if the ID is used more then once and just returns '1 Order' on all rows.
Where does this go wrong ?
There is a reference made to 'the Account[ID]' but this column was placed in the Project table and not the Account table so this formula looks in the Account table, which would explain why it only finds 1 hit each time. I think this formula is missing the calculation in the Project table
You changed the name of the column that's being counted from my original suggestion. I have no idea what Account[ID] is. Is that an order ID? Client ID? There should be a relationship based on a client ID that appears in both table, and you should be counting the order IDs with the same client ID. I'm still very unclear about the contents of your two tables and how they are related. Show me what the columns are in both tables and which columns form the relationship between them.
Proud to be a Super User!
i didn't change anything, i just copied/pasted your formula.
Hopefully this will shed some light on the problem
The Project table looks like this, i marked the ID (which is the Order.iD) and the Account.ID (which is the client.iD)
this is the Account table, where i marked the ID (in the table the Client.Id) and i hid the clients names for obvious reasons
I made few simple ralationships between the 3 tables
@RvdHeijden you said earlier that Account was the table that had all the orders.
1. In the table of Account is a list of all the orders which have been placed witch their own (order)ID 2. In the table of Project are alle the clients with their own (Client) ID
Now you're saying the opposite.
The Project table looks like this, i marked the ID (which is the Order.iD) and the Account.ID (which is the client.iD)
this is the Account table, where i marked the ID (in the table the Client.Id) and i hid the clients names for obvious reasons
No wonder the formula isn't working. The formula is supposed to count all of the the order IDs for each client ID but you keep trying to count the client IDs instead.
You did change the column in my formula. My formula said COUNTA(Account[OrderID]) not COUNTA(Account[ID]). OrderID. Because we're trying to count orders. Not clients. But it hardly matters because now you're telling me that Account doesn't have any OrderIDs at all.
I still can't see from your screenshots how that relationship works. Before I try to rewrite the formula I need to know for certain where things are and what they are. Can you please confirm for me the following:
Proud to be a Super User!
My appologies for the misunderstanding, it looks like i somehow mixed things up.
Confirmed
Confirmed
Confirmed, see below
I want a count that bundles clients on how many orders they placed. i want to group them in groups such as '1 order', 'between 2 and 5 orders', 'between 6 and 10 orders', etc
So basically the formula needs to check a)how many orders dit this client place and b) in which categorie it falls in ('1 order', 'between 2 and 5 orders' etc)
Hopefully this clears up a few things and again sorry for the misunderstanding
@RvdHeijden add this column to the Account table:
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' )
Proud to be a Super User!
This column should be in the Project table i think because that is the table which holds all the project. The account table hold all the clients.
Nevertheless i used this formula but it returns the error: 'Cannot find table '1 order'
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' )
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" )
Proud to be a Super User!
Can i adapt this formula to simply give a number on how many orders a client has placed ?
The current formula bundles the outcome in different categories but what should the formula be if i simply want a colum (in the Account table) which calculates (per client) how many orders did they place ?
WOw....i surprised myself because ive adapted your formula and it almost gives the right values (i think anywayz)
But the only problem is that it also returns blank rows,
Number of Orders2 = CALCULATE( COUNTA(Project[ID]))
If i check the results i see that the formula gives blank values in situations were the previous formula return the value of 'More then 20 orders' in all other cases it returns a correct value.
EDIT: sometimes it gives a value if the value was 'More then 20 orders' not always but sometimes
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |