Forum Discussion
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
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
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.
6 Replies
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
IF(COUNTROWS(FILTER(V_DimCommissionAgreement,V_DimCommissionAgreement[Agreement Commissionpercent] = V_DimCommissionAgreement[Description5])) > 0,0,1)
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.
