Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RvdHeijden
Post Prodigy
Post Prodigy

How 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.

1 ACCEPTED 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"
 )

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

30 REPLIES 30
Anonymous
Not applicable

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

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"
)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

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'
 )




Did I answer your question? Mark my post as a solution!

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'
 )




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

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)Project_table.png

 

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

 

Account_table.png

 

 I made few simple ralationships between the 3 tables

 

Relationships.png

 

 

 

 

@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:

 

  1. The Account table is a table of clients, where each client ID appears only once, and there are no orders in the Account table.
  2. The Project table is a table of orders, not clients, where each order ID appears only once and client IDs may appear more than once if the client has multiple orders
  3. The relationship between the tables is formed by the columns Project[AccountId] and Account[ID]. It is a 1 to Many relationship, where Account[ID] is the 1 and Project[AccountId] is the many.
  4. You want a count of orders from the Project table that correspond with each client in the Account table.

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

My appologies for the misunderstanding, it looks like i somehow mixed things up.

 

  1. The Account table is a table of clients, where each client ID appears only once, and there are no orders in the Account table.

Confirmed

 

  1. The Project table is a table of orders, not clients, where each order ID appears only once and client IDs may appear more than once if the client has multiple orders

Confirmed

 

  1. The relationship between the tables is formed by the columns Project[AccountId] and Account[ID]. It is a 1 to Many relationship, where Account[ID] is the 1 and Project[AccountId] is the many.

Confirmed, see below

 

  1. You want a count of orders from the Project table that correspond with each client in the Account table.

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

 

relationships1_001.png

 

 

@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'
 )




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

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"
 )

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman

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 ?

@KHorseman

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

@KHorseman

Any ideas about this problem ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.