Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
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
Solved! Go to Solution.
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
Proud to be a Super User! | |
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.
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.
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
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
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
67 | |
61 | |
46 | |
45 |