Forum Discussion

Pavithra_sarava's avatar
Pavithra_sarava
Regular Visitor
3 years ago
Solved

Need to find order without product without using calculated columns/tables

Hello All,

I've 2 table in my report.

 

Order table: Order ID,Order name, value, Products,etc

Order detail table: Order ID,Product name, Specification, etc

 

There's a relationship between Order & Order detail table (One to many)

I need to find number of order without products. Here I can't use calculated column or table as my source is direct query and I won't be able to do auto refresh.

Can someone suggest me how to get the desired result? 

Need Help
  • lukiz84's avatar
    lukiz84
    3 years ago

    How is an order created if there are no products? 

     

    Try 

    OrdersWithoutProducts =
   CALCULATE(
      COUNT(Orders[Order ID]),
      NOT(Orders[Order ID] IN VALUES('Order detail'[Order ID]))
   )

3 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    If you have a one-to-many relationship from orders to order details then you could use

    # orders no products =
SUMX ( 'orders', IF ( ISEMPTY ( RELATEDTABLE ( 'order details' ) ), 1 ) )
      • lukiz84's avatar
        lukiz84
        Icon for Memorable Member rankMemorable Member
        3 years ago

        How is an order created if there are no products? 

         

        Try 

        OrdersWithoutProducts =
   CALCULATE(
      COUNT(Orders[Order ID]),
      NOT(Orders[Order ID] IN VALUES('Order detail'[Order ID]))
   )