Look for the values missing in tables

[vnqt]

Hello,

I have 4 tables

Table 1	Table 2	Table 3	Table 4
Serial number	Serial number	Serial number	Serial number
SN1	SN21	SN10	SN16
SN2	SN22	SN11	SN17
SN3	SN23	SN12	SN18
SN4	SN24	SN13	SN19
SN5	SN25	SN14	SN20
SN6	SN26	SN48	SN68
SN7	SN27	SN49	SN69
SN8	SN28	SN50	SN70
SN9	SN29	SN51	SN71
SN10	SN30	SN52	SN72
SN11	SN1	SN20	SN73
SN12	SN2
SN13	SN3
SN14	SN4
SN15	SN5
SN16	SN6
SN17	SN7
SN18	SN8
SN19	SN9
SN20

I would like to have a list and the number of Serial numbers : 

- Serial number (table 1)  missing in Table 2 : 11

SN10

SN11

SN12

SN13

SN14

SN15

SN16

SN17

SN18

SN19

SN20

_ 11 serial numbers (above) :  There are 6 missing in table 3 

SN15

SN16

SN17

SN18

SN19

SN20

_ 6 serial numbers missing in table 3 : There is 1 (SN15) missing in table 4

Could you plase advise the solution ? 

Many thanks in advance. 

Tg

[tamerj1]

Hi @vnqt 
Somthing like 

Card1 =
CONCATENATEX (
    EXCEPT ( VALUES ( Table1[SN] ), VALUES ( Table2[SN] ) ),
    [SN],
    UNICHAR ( 10 )
)

Card2 =
CONCATENATEX (
    EXCEPT (
        EXCEPT ( VALUES ( Table1[SN] ), VALUES ( Table2[SN] ) ),
        VALUES ( Table3[SN] )
    ),
    [SN],
    UNICHAR ( 10 )
)

Card3=
CONCATENATEX (
    EXCEPT (
        EXCEPT (
            EXCEPT ( VALUES ( Table1[SN] ), VALUES ( Table2[SN] ) ),
            VALUES ( Table3[SN] )
        ),
        VALUES ( Table4[SN] )
    ),
    [SN],
    UNICHAR ( 10 )
)

[vnqt]

Hi, 

thank you for your reply. The measure shows the SN name but doesn't count the SN, and I can't filter it . 

Could you please advise another sugestion ? 

Tg

[tamerj1]

Hi @vnqt 

if you want to show both you can do for example

 Card3 =
VAR T1 =
EXCEPT (
EXCEPT (
EXCEPT ( VALUES ( Table1[SN] ), VALUES ( Table2[SN] ) ),
VALUES ( Table3[SN] )
),
VALUES ( Table4[SN] )
)
VAR Result1 =
CONCATENATEX ( T1, [SN], UNICHAR ( 10 ) )
VAR Result2 =
COUNTROWS ( T1 )
RETURN
Result1 & UNICHAR ( 20 ) & "Total of " & Result2

[tamerj1]

Hi @vnqt 

how would you like to disply the results? Which type of visuals and in which way? Or you're just looking for a calculated table or separate tables?

[vnqt]

Hi @tamerj1 

The result would be the card for the missing numbers and table for each card

- Serial number (table 1)  missing in Table 2 : 11

SN10

SN11

SN12

SN13

SN14

SN15

SN16

SN17

SN18

SN19

SN20

_ 11 serial numbers (above) :  There are 6 missing in table 3 

SN15

SN16

SN17

SN18

SN19

SN20

_ 6 serial numbers missing in table 3 : There is 1 (SN15) missing in table 4

Thank you in advance for your help