cancel
Showing results for
Did you mean:
Helper III

## Look for the values missing in tables

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 ?

Tg

5 REPLIES 5
Super User

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 )
)``````

Helper III

Hi,

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

Tg

Super User

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

Super User

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?

Helper III

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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors