Forum Discussion
Create Measure between Multiple tables
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.
Please, check data types of the SN column in all tables. It should be whole number.
Here I used another table without one of the values to make sure it works:
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
Hi gaurav-narchal ,
Do I understand correctly that Serial No from Data Source table can be met only at one of the tables?
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.
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
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.