Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Comparing two table columns which is having Many to many relationship

 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 

Help appreciated
Help me
Help Requested
  • VahidDM's avatar
    VahidDM
    4 years ago

    Anonymous  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/

     

     

12 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous 

     

    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/

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

    • VahidDM's avatar
      VahidDM
      Icon for Super User rankSuper User
      4 years ago

      Anonymous 

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

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

         

         

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.