Comparing two table columns which is having Many to many relationship

[THENNA_41]

 i have a two table in power bi .  i want to compare the values between two tables if values is matched it should return True  . if values not matched its should be return false .

My table 1:

SID      columntype       IN table            EO_IN            EO_OUT         year 

241      Adjust               750                   750                750                2021 

241      Ship                  250                    450                454                2021 

 241      Adjust              250                   450                 454               2022 

241      Ship                 250                     250                 250               2022

My table 2 : 

columntype        year    CMN

Adjust                2021     750

Adjust                2022     454

Ship                   2021      454

Ship                   2022     250

i have a created  relationship between two tables 

Now table looks like  below 

SID      columntype       IN table            EO_IN            EO_OUT         year     CMN

241      Adjust               750                   750                750                2021      750

241      Ship                  250                    450                454                2021      454

 241      Adjust              250                   450                 454               2022       454

241      Ship                  250                     250                 250              2022      250

Now i want to create new column in the table   . if values matched its return true  or if values are not matached return false . look like below 

SID      columntype       IN table            EO_IN            EO_OUT         year     CMN    Status

241      Adjust               750                   750                750                2021      750      True 

241      Ship                  250                    450                454                2021      454      Fasle 

 241      Adjust              250                   450                 454               2022       454       Fasle 

241      Ship                  250                     250                 250              2022      250      True

 tired with realted and tealtedtable() function but getting error . any idea . thanks in advance 

[THENNA_41]

@VahidDM  i am not able to type calculate funcution in new column sir . its getting error 

[VahidDM]

@THENNA_41 

It works well for me, can you share your PBIX file, I think it's more complicated 

[THENNA_41]

@VahidDM  pls find the below link for file .Tap name summary .thank you so much 

[VahidDM]

@THENNA_41 

You file has Directquery and I can't see your data and visuals.

Try this as a measure:

Status = 
VAR _A =
    CALCULATE(
        MAX( vw_IntegrationDataCheck [CMN] ),
        FILTER(
            vw_IntegrationDataCheck ,
            vw_IntegrationDataCheck[ColumnType]  = MAX( Out_DataComparison_Totals[columntype] )
                && vw_IntegrationDataCheck[CalendarYear] = MAX( Out_DataComparison_Totals[CalendarYear] )
        )
    )
RETURN
    IF( max(Out_DataComparison_Totals[EO_IN_Tables]) = _A, TRUE(), FALSE() )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[THENNA_41]

@VahidDM   i have tired in measure i am able to type calcualte function .   but values return only checking column type and Year . i want to check three values also .if three values and  scond table CMN values matched should return True  sir 

[VahidDM · Accepted solution]

@THENNA_41  OK try this, and update the column names in the last row of the measure 

Status =
VAR _A =
    CALCULATE(
        MAX( vw_IntegrationDataCheck[CMN] ),
        FILTER(
            vw_IntegrationDataCheck,
            vw_IntegrationDataCheck[ColumnType]
                = MAX( Out_DataComparison_Totals[columntype] )
                && vw_IntegrationDataCheck[CalendarYear]
                    = MAX( Out_DataComparison_Totals[CalendarYear] )
        )
    )
RETURN
    IF(
        MAX( Out_DataComparison_Totals[EO_IN_Tables] ) = _A
            && MAX( Out_DataComparison_Totals[EO_Out_Tables] ) = _A
            && MAX( Out_DataComparison_Totals[ IN_Tables] ) = _A,
        TRUE(),
        FALSE()
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[THENNA_41]

@VahidDM  sir modifed the measure .   true  return only for the all   four below values  same 
Eo_IN_tables,Eo_Out_tables,IN_Tables,CMN  
please find the below screen shot for  your  reference . its true or false wrongly  returned sir 

[VahidDM]

@THENNA_41  what is the aggregation of these Eo_IN_tables,Eo_Out_tables,IN_Tables columns in that Matrix/Table?

and it seems the result should be false in that image your shared?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[THENNA_41]

@VahidDM  sir i have used iun the table format . its all in don't summarize . True status conditional all the four value should be then only it will be true other wise its false 

Example :

Eo_IN_tables   Eo_Out_tables      IN_Tables       CMN               Status 

 22951661      22951661             22951661       22951661          True 
 22951661       4928060              4776026.39    4776026.39      False 

[VahidDM]

Hi @THENNA_41 

Try this code to add a new column:

Status =
VAR _A =
    CALCULATE(
        MAX( 'Table (2)'[CMN] ),
        FILTER(
            'Table (2)',
            'Table (2)'[columntype] = EARLIER( 'Table'[columntype] )
                && 'Table (2)'[year] = EARLIER( 'Table'[year] )
        )
    )
RETURN
    IF( [EO_IN] = _A, TRUE(), FALSE() )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[ValtteriN]

Hi,

It would be good to have more information e.g. which column you are using to create relationship and what kind of error you are getting. Regardless I think you can use the following structure:

If(lookupvalue(search column, add two conditions here (4 columns in total))=blank(),False,True)

So the idea here is to check if lookupvalue matches columns in your search table and if it does return true/false.

[THENNA_41]

@ValtteriN  i have used  columntype  column for relationship  between  two tables Many to Many Relationship. i am getting below Error 

The column 'My table 2 [CMN]' either doesn't exist or doesn't have a relationship to any table available in the current context.