Forum Discussion
Count based on two columns in two different tables
Hi All,
Below are my tables, Order table and Cart table are related by Order ID. Cart and Message tables are related by Cart ID.
My requirement is to count the number of Orders by messages. I am expecting below output:
| Number of Orders | Messages |
| 2 | Your order is not correct |
Can anyone help me how to get this output in DAX ?
Regards,
Shiva Kumar
Hi ncbshiva
If you use the [Order ID] column from Cart table and use the aggregate type Count (or Count Distinct), you will get the result directly.
Or you can also create a measure Measure1 = COUNT(Cart[Order ID]) or Measure1 = DISTINCTCOUNT(Cart[Order ID]) and put it in the table.
To use both of the above solutions, the relationship's cross-filter direction between Message table and Cart table should be Single (Message table filters Cart table) or Both.Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
3 Replies
ncbshiva , Hop message, and cart are joined
ithink count should do
calculate(count(cart[order id] , not(isblank(Message[cart id])))
if needed
calculate(distinctcount(cart[order id] , not(isblank(Message[cart id])))
visualize with Message[Message]
Hi,
Thanks for the message.
As mentioned Cart and Message table is joined/related by Cart ID.I tried the dax that you have provided, but i did not get the expected results.
Hi ncbshiva
If you use the [Order ID] column from Cart table and use the aggregate type Count (or Count Distinct), you will get the result directly.
Or you can also create a measure Measure1 = COUNT(Cart[Order ID]) or Measure1 = DISTINCTCOUNT(Cart[Order ID]) and put it in the table.
To use both of the above solutions, the relationship's cross-filter direction between Message table and Cart table should be Single (Message table filters Cart table) or Both.Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
