find the True or False compare two table using common column in both table

[THENNA_41]

I have two table First table and second table. I am trying to compare two table based on the common column  Itemcode in two table.

1. If First table  Item code have pack outer value and same time second table Same Item code  have BOM text value its return   TRUE.
2. If First table  Item code have pack outer value its blank and same time second table Same Item code  have BOM text value  its return  FALSE
3. If First table  Item code have pack outer value its blank and same time second table Same Item code  have BOM text   value blank its return  TRUE
4. If First table  Item code have pack outer value or  blank and same time second table doesn't contain same Item code its return TRUE

First Table :

Itemcode                   Pack outer     

FA411211                          56

FA411212                          46

FA411213                          44

FA411214                          

FA411215                          46

FA411216                         

Second Table :

Itemcode                  BOMTEXT 

FA411211                       PL

FA411212                       PW

FA411213                          

FA411214                          

FA411216                       TH

Expect Output: 

Itemcode                   Pack outer      TRUE/FALSE 

FA411211                          56              TRUE

FA411212                          46              TRUE 

FA411213                          44              TRUE 

FA411214                                            TRUE

FA411215                          46              TRUE

FA411216                                            FALSE

looking for support . thanks in advance 

[v-yueyunzh-msft · Accepted solution]

Hi , @THENNA_41 

Thank you for your sample data and detailed description for yoor need.

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can click "New Column" and enter:

True/False = var _cur_code = [Itemcode]
var _second_code =COUNTROWS(FILTER('Second Table','Second Table'[Itemcode]=_cur_code))
var _pack = [Pack outer]
var _bomtext = MAXX( FILTER('Second Table','Second Table'[Itemcode]=_cur_code ) ,[BOMTEXT])
return
IF(_second_code<=0 , "True" , IF(_pack<> BLANK() ,"True" , IF(_bomtext<> BLANK() ,"False" ,"True")))

(3)Then we can meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[THENNA_41]

@v-yueyunzh-msft  i am trying  to modify the above result for Itemcode conditions  FA411213 and FA411215  . if Value  available in  First table and same time second table is blank or item code missing its a False .

[THENNA_41]

@v-yueyunzh-msft  its working .. thank you for your help.

[THENNA_41]

@v-yueyunzh-msftone modificatiosn one measure . if First table item code  if not available in Second table its should be False .. 

[Arul]

@THENNA_41 ,

try this formula in calculated column,

TRUE/FALSE = 
VAR _lookup =
    LOOKUPVALUE (
        'Second Table'[BOMTEXT],
        'Second Table'[Itemcode], 'First Table'[Itemcode]
    )
VAR _result =
    SWITCH (
        TRUE (),
        'First Table'[Pack outer] <> BLANK ()
            && _lookup <> BLANK (), TRUE (),
        'First Table'[Pack outer]
            == BLANK ()
                && _lookup <> BLANK (), FALSE (),
        'First Table'[Pack outer] = BLANK ()
            && _lookup = BLANK (), TRUE (),
        TRUE ()
    )
RETURN
    _result

Thanks,

Arul