Forum Discussion
Comparing values from two floating tables
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
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 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
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