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 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.
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 ERD - Some of the serial numbers are only aplhabets (JKHTGBLR) or blank how can i ignore them through the same measure.
Thank you for all your help.