Create Measure between Multiple tables

[gaurav-narchal]

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.

Click here PBIX

Thanks

Gaurav

[ERD · Accepted solution]

@gaurav-narchal ,

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.

[ERD]

Hi @gaurav-narchal ,

Do I understand correctly that Serial No from Data Source table can be met only at one of the tables?

[gaurav-narchal]

Hi @ERD - Yes Serial number from Data Source table can be met only at one of the tables. 

Thank You.

[ERD · Accepted solution]

@gaurav-narchal ,

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]

@gaurav-narchal ,

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.

[gaurav-narchal]

Hi @ERD 

This measure is not working. 

[ERD · Accepted solution]

@gaurav-narchal ,

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.

[gaurav-narchal]

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.

[ERD]

@gaurav-narchal ,

It doesn't metter which type - Whole number or Text. The idea is that all the SN columns are of the SAME type.

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.