Calculated fields using data from different table

[HCK21]

HI,

I have two tables; one with customer data and one with order data.

I would like to be able to add a column (like the one in red) to the customer table to show whether all orders are fulfilled for that custom, based on the following rules:

• If a customer doesn't appear in the order table at all then "No orders"
• If one or more orders for that customerid have a row that says "no" in fulfilled column, then All Orders Fulfilled should say "Unfulfilled orders"
• If all orders in order table say for that customerID say "Yes", then "All orders Fulfilled" 

CustomerId	CustomerName	All orders fulfilled
1	Stella	No orders
2	Joan	All orders fulfilled
3	Marge	Unfulfilled orders

OrderID	CustomerID	Order	Fulfilled
1	2	Pants	Yes
2	3	Pants	Yes
3	4	Shoes	No
4	2	Shoes	Yes
5	2	Socks	Yes
6	3	Shoes	No
7	6	Pants	No

[HCK21]

Thanks @parry2k - this works like a charm

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = if(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[CustomerID]=EARLIER(Data[CustomerId])))=BLANK(),"No orders",if(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[CustomerID]=EARLIER(Data[CustomerId])&&'Table'[Fulfilled]="Yes"))=CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[CustomerID]=EARLIER(Data[CustomerId]))),"All orders fulfilled","Not fulfilled"))

Hope this helps.

[parry2k · Accepted solution]

Sorry there is a typo, it suppose to be FIlTER not FILTERS

[HCK21]

HI @parry2k 

Thanks for the speedy response. I've change the table/column names in your suggestion to reflect my column names etc, but I'm getting an error on this part of the statement. PowerBI error says "Parameter is not the correct type" and suggests this part of the arguement should be a table. I think i'm probably missing something obvious.

FILTERS ( __Orders, OrderTable[Fullfilled Column] = "No"  )

[parry2k]

@HCK21 try following calculated column in the customer table:

All Orders Fulfilled = 
VAR __Orders = RELATEDTABLE ( OrderTable )
VAR __CountNo = COUNTROWS ( FILTERS ( __Orders, OrderTable[Fullfilled Column] = "No"  ) )
RETURN
SWITCH ( TRUE (),
   ISEMPTY ( __Orders ), "No Orders",
   NOT ISBLANK ( __CountNo ), "Unfulfilled Orders",
   "All orders fulfilled"
)