Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
jostnachs
Helper IV
Helper IV

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 description column as 

jostnachs_1-1723214729227.png where the 3rd field (www) is my department. Then I have Structure table where there is local department column.

Now, Based on the selected value from Local Department column ,if it matches with description column then add a new column in the agreement table as 1 else 0. 

I tried the below formula but it is giving me a column with only 1s. Can someone help me here please

jostnachs_2-1723214973843.png

 

 

jostnachs_0-1723214646149.png

 

2 ACCEPTED SOLUTIONS
rajendraongole1
Super User
Super User

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

 

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

Anonymous
Not applicable

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.

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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.

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.

jostnachs_1-1723653120104.png

 

Anonymous
Not applicable

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.

 

Thank you.... The problem is fixed

rajendraongole1
Super User
Super User

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

 

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Hi @rajendraongole1  Thanks for your reply. I tried something but it is givng me wrong values. could u please help me to understand where I am going wrong. I have to compare the description5 coming from description field to my selected commission percent column and add column if they are same, give me 0 else give me 1. if u see below screenshot, even when my description5 and agreementcommissionpercent are different, it gives me 0. Even after using the formula given above, it gives me same result. all the columns are in the same table

 

IF(
COUNTROWS(
FILTER(
V_DimCommissionAgreement,
V_DimCommissionAgreement[Agreement Commissionpercent] = V_DimCommissionAgreement[Description5]
)
) > 0,0,1
)
 

jostnachs_0-1723651882259.png

 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.