Forum Discussion

jostnachs's avatar
jostnachs
Helper IV
2 years ago
Solved

Comparing values from two floating tables

Hi All, I have two tables without a relationship between them. But i want to compare two columns from table1 and table2  and add a new column in table 1. i.e.  I have Agreements Table with a descr...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi jostnachs - First, you'll want to create a calculated column in the Agreements table to extract the department from the "Description" column.

    Department Extracted =
    PATHITEM(SUBSTITUTE(Agreements[Description], "_", "|"), 3, TEXT)

    Assuming the department is the third part when split by underscores (_), you can use the PATHITEM function after splitting the text.

     

    create a new calculated column in the Agreements table that checks whether the extracted department matches any department in the structure table.

     

    Matched Department =
    IF(
    COUNTROWS(
    FILTER(
    Structure,
    Structure[Local Department] = Agreements[Department Extracted]
    )
    ) > 0,
    1,
    0
    )

     

    Hope it works to reports as expected 1 or 0

     

     

     

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jostnachs 

    You can create a measure.

    Sum_up =
SUMX ( VALUES ( DimCommissionAgreement[Agreement Description] ), [Measure1] )

    Best Regards!

    Yolo Zhu

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

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,

Thanks for the solution rajendraongole1 offered, and i want to offer some more information for user to refer to.

hello jostnachs , based on your description, if you want to add a column that based on the selection you have selected, it is better that use the measure instead of the calculated column, you can refer to the following measure.

Measure =
VAR a =
    COUNTROWS (
        FILTER (
            VALUES ( V_DimStructure[Local Depaetemt] ),
            containstring (
                SELECTEDVALUE ( DimCommissionAgreement[Agreement Description] ),
                [Local Depaetemt]
            )
        )
    )
RETURN
    IF ( a > 0, 1, 0 )

Then put the masure and the description field to a visual ,such as table visual, then the measure can work.

 

Best Regards!

Yolo Zhu

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

  • jostnachs's avatar
    jostnachs
    Helper IV
    2 years ago

    Hi Yolo,

    Thanks for the response. I tried this but I would want a column because i have to add the values (1 and 0) and get the sum of discrepencies. and show it as a card. giving me correct results but how can i add those 1 and 0 to get sum.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi jostnachs 

      You can create a measure.

      Sum_up =
SUMX ( VALUES ( DimCommissionAgreement[Agreement Description] ), [Measure1] )

      Best Regards!

      Yolo Zhu

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

       

      • jostnachs's avatar
        jostnachs
        Helper IV
        2 years ago

        Thank you.... The problem is fixed