Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vnqt
Helper IV
Helper IV

Look for the values missing in tables

Hello,

I have 4 tables

 

Table 1Table 2Table 3Table 4
Serial numberSerial numberSerial numberSerial number
SN1SN21SN10SN16
SN2SN22SN11SN17
SN3SN23SN12SN18
SN4SN24SN13SN19
SN5SN25SN14SN20
SN6SN26SN48SN68
SN7SN27SN49SN69
SN8SN28SN50SN70
SN9SN29SN51SN71
SN10SN30SN52SN72
SN11SN1SN20SN73
SN12SN2  
SN13SN3  
SN14SN4  
SN15SN5  
SN16SN6  
SN17SN7  
SN18SN8  
SN19SN9  
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

5 REPLIES 5
tamerj1
Super User
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 )
)

 

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

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
Super User
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?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors