Help Needed

[Waqas_BIspecs]

Hi, 

In the below image you can see i have a pallet_id_from column where there are multiple instances appearing against these in if_tran_code column. i was to create another calculated column where i can mark all these instances against the Pallet-_id as "Shipped" if there there is one instance i.e. "FO-LOAD" exists against this column. 

Note: This data is coming through Direct Query from SQL.

Required Output:

Thank you

[tamerj1 · Accepted solution]

Hi @Waqas_BIspecs 

Please try

Status =
IF (
"FO-LOAD"
IN CALCULATETABLE (
VALUES ( 'Table'[if_tran_code] ),
ALLEXCEPT ( 'Table', 'Table'[pallet_id_from] )
),
"Shipped"
)

[Waqas_BIspecs]

Hi, 

Its making everything "shipped"

[tamerj1 · Accepted solution]

@Waqas_BIspecs 

Yes you are right. The small sample of data was a little misleading. You need to add activity-date to ALLEXCEPT 

Status =
IF (
"FO-LOAD"
IN CALCULATETABLE (
VALUES ( 'Table'[if_tran_code] ),
ALLEXCEPT ( 'Table', 'Table'[pallet_id_from], 'Table'[activity-date] )
),
"Shipped"
)

[tamerj1 · Accepted solution]

Hi @Waqas_BIspecs 

Please try

Status =
IF (
"FO-LOAD"
IN CALCULATETABLE (
VALUES ( 'Table'[if_tran_code] ),
ALLEXCEPT ( 'Table', 'Table'[pallet_id_from] )
),
"Shipped"
)