Count based on two columns in two different tables

[ncbshiva]

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

[v-jingzhang · Accepted solution]

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.

[amitchandak]

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

[ncbshiva]

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.