Forum Discussion
Helper ICreate Measure between Multiple tables
Community ChampionHi gaurav-narchal ,
Do I understand correctly that Serial No from Data Source table can be met only at one of the tables?
Helper I- ERD
One of the ways is to use these measures (I assume your tables are connected via Serial No column):
#check = VAR _t = UNION('Table 1','Table 2','Table 3','Table 4') VAR amt = SUMX(_t, [AMOUNT]) VAR dsAmt = SUM('Data Source'[AMOUNT]) RETURN IF(dsAmt = amt, "OK", "Error")#diff = VAR _t = UNION('Table 1','Table 2','Table 3','Table 4') VAR amt = SUMX(_t, [AMOUNT]) VAR dsAmt = SUM('Data Source'[AMOUNT]) RETURN IF([#check] = "Error", amt - dsAmt, 0)If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
- gaurav-narchal
Hi ERD
I'm almost there. I also need to validate if the serial number does exist in the Data Source table and Table 1 , 2 , 3 and 4. How can i validate this through measure?
Measure = If Data Source (Serial No) Matches [tables] Table 1 ,2 3 & 4 (Serial No) return “ok” else “Error”#check =
VAR _t = UNION('Table 1','Table 2','Table 3','Table 4')
VAR amt = SUMX(_t, [AMOUNT])
VAR dsAmt = SUM('Data Source'[AMOUNT])
RETURN
IF(dsAmt = amt, "OK", "Error")Thanks
- ERD
You can use this measure:
#checkIfExists = VAR currentSN = SELECTEDVALUE('Data Source'[SERIAL NO]) VAR _t = UNION ( DISTINCT ( 'Table 1'[SERIAL NO] ), DISTINCT ( 'Table 2'[SERIAL NO] ), DISTINCT ( 'Table 3'[SERIAL NO] ), DISTINCT ( 'Table 4'[SERIAL NO] ) ) RETURN IF(currentSN IN _t, "OK", "Error")If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.