Dax Query to find Missing Items from a List

[Sam_BI_Analyst]

Hello Community,

I need help with finding missing items from a list of items, Below is my dataset and problem.

I have students dataset and i want to find the missing subjects for each student,

Student Data - 

Subjects Table-

Requirement - I want to find the missing subjects for each student, Let's take Lyon for instance, Lyon had registered only one subject (Maths), Now i want to find he missing subjects for him from the subjects table i.e., Science, english, biology, physics.

My Result dataset should be,

How do i achieve the above result in power bi? Any suggestions would be appreciated.

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOztRRcM1L11EIyKhUitWJVjICivomlmToKDhl5oNFjCHqwGwTJD1gAVOocqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Subjects = _t]),
    Subject = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7OVIrViVbyTSzJADNc89LBtFNmPpgOyKhUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subject = _t]),

    #"Added Column Missing" = let s = Subject[Subject] in Table.AddColumn(Source, "Missing", each let l = List.Transform(Text.Split([Subjects],","), Text.Trim) in Text.Combine(List.Difference(s, l), ", "))
in
    #"Added Column Missing"

[Ritaf1983 · Accepted solution]

Hi @Sam_BI_Analyst 
You can achieve the desired results with those steps :
1. From PQ split the table of registered :

The result that you should get is :

2. create a relationship between this table the subjects table :

3. Create 2 Measures :

Registered = CONCATENATEX('Table','Table'[Subjects Registered],",")

Missing =

var _registered = values('Table'[Subjects Registered])

var _allsubjects = all('Subjects'[Subjects])

RETURN

CONCATENATEX(EXCEPT(_allsubjects,_registered),Subjects[Subjects],",")

Result :

PBIX is attached

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

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOztRRcM1L11EIyKhUitWJVjICivomlmToKDhl5oNFjCHqwGwTJD1gAVOocqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Subjects = _t]),
    Subject = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7OVIrViVbyTSzJADNc89LBtFNmPpgOyKhUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subject = _t]),

    #"Added Column Missing" = let s = Subject[Subject] in Table.AddColumn(Source, "Missing", each let l = List.Transform(Text.Split([Subjects],","), Text.Trim) in Text.Combine(List.Difference(s, l), ", "))
in
    #"Added Column Missing"

[Sam_BI_Analyst]

Can you explain what did you do for add column in the end