Check If Column value of one table matches with column value of another table then perform operation

Check If Column value of one table matches with column value of another table then perform operationFollow
Prince Kumar
Prince KumarAug 23, 2022 02:32 AM
Hi Guys, I have a table that is having some divisions values named tableA(this is used in matrix) …
1. Check If Column value of one table matches with column value of another table then perform operation

Prince Kumar
Posted Aug 23, 2022 02:32 AM
Hi Guys,

I have a table that is having some divisions values named tableA(this is used in matrix)
Bakery
Camera
Food
Household
Chairs
Fans
I have target table ,that is having targets based on each division for this year
Division Target_0 Target_50 Target_100 Target_150 Year
Bakery 0.36 0.38 0.41 0.46 2022
Camera 0.52 0.54 0.56 0.61 2022
Food 0.48 0.5 0.52 0.57 2022
Household 0.36 0.39 0.42 0.48 2022
I have measure that is having these values and i need to check if divisions(table A) is present in division of target table then match it’s corresponding rows to measure

if the measure is less target_50 column then true else false.

I created measure but this works only for one row, i was trying to compare tableA[Division] with ‘Target table'[Division] directly through related also ,it isn’t working

VAR CHECK_RED=IF (FIRSTNONBLANK((tableA[Division]), 1) =FIRSTNONBLANK((‘Target table'[Division]), 1) &&
FIRSTNONBLANK((‘Yearly Targets'[Year]), 1)=”2022″,
IF(FORMAT(‘View By'[Measure],”Percent”)<FORMAT(FIRSTNONBLANK((‘Target table'[Target_50_Percent]),1),”Percent”),True,False),False)
RETURN
CHECK_RED

Could u please guide me how we can compare 2 columns here? Just need comparison for 2 columns

——————————
Prince Kumar
Consultant- Cloud Data Engineer
——————————

2. RE: Check If Column value of one table matches with column value of another table then perform operation

Kaz Shakir
Posted Aug 29, 2022 08:06 PM | view attached
@Prince Kumar,

It would be easier to answer your question if you post a .pbix file with a sample of your data and your data model.

Based on what you described, I tried to create what I think your data and model look like.​ I think you have three tables like so:
Division:

Target:

Results:

And the data model might look like this:

In that case, you could add two calculated columns to the Divisions table, that would have formulas like this:
Does Division Have Target_50 =
NOT(ISBLANK(RELATED(target[Target_50])))

Are results for division less than Target_50 =
IF(
divisions[Does Division Have Target_50],
IF(RELATED(results[Value]) < RELATED(target[Target_50]), “True”, “False”),
BLANK()
)​

And then you can create a table visual to show the results:

Hope that helps. Please consider posting your ,pbix file, and we will be able to give you a more customized answer.

Kaz.

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixprincekumar_example_1.pbix