Forum Discussion
Create Measure between Multiple tables
I want to create a measure to get the below result
Measure = If Data Source (Serial No) & Amount Matches [tables] Table 1 (Serial No) & Amount or Table 2 (Serial No) & Amount or Table 3 (Serial No) & Amount or Table 4 (Serial No) & Amount return “ok” else “Error”
Then calculate the Amount difference and show in the last column
Result Display
| SERIAL NO | AMOUNT | STATUS | AMOUNT DIFFERENCE |
| 9090939560 | 1985 | OK | 0 |
| 9090939561 | 1985 | OK | 0 |
| 9090939562 | 1985 | OK | 0 |
| 9090939563 | 1985 | OK | 0 |
| 9090939564 | 4755 | OK | 0 |
| 9090939565 | 1170 | Error | 170 |
| 9090939566 | 1085 | OK | 0 |
| 9090939567 | 1855 | Error | 5 |
| 9090939570 | 2595 | OK | 0 |
Please find below the sample if this helps.
Thanks
Gaurav
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.
9 Replies
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.
