missing data but with additional information from both tables

[AndrewPF]

If I have a list of standard controls as follows: 

Control Set

HR Security

Compliance

Web Security 

Incident Response 

and a separate list of businesses, along with their own controls:  

Company           Control Set

Fever Tree          HR Security 

Fever Tree          Compliance 

Fever Tree          Incident Response

Britvic                 HR Security

Britvic                 Compliance

Britvic                 Web Security

Thwaites             HR Security

Thwaites             Compliance 

Thwaites             Web Security

Thwaites             Incident Response 

PepsiCo              HR Security 

PepsiCo              Web Security 

PepsiCo              Incident Response 

then I need a way of identifiying which business have missing controls, and which controls are missing from each business, i.e.: 

Company       Control Set 

Fever Tree       Web Security 

Britvic              Incident Response 

PepsiCo           Compliance

Any ideas please? 

[AndrewPF]

Unfortunately, I have 33 control sets and 200 business units, so a matrix or table just won't work graphically. 

The requirements from my customer are very specific i.e. a list of all missing control sets from each business unit which is missing at least one control set. 

[lbendlin]

Missing = CONCATENATEX(EXCEPT(SELECTCOLUMNS('Control Set Reference',"Name",[Control Set Name]),VALUES('Control Sets'[Name])),[Name],",")

see attached

[AndrewPF]

First table name: "Control Sets": 

Business Unit	Name
Fever Tree	HR Security
Fever Tree	Compliance
Fever Tree	Incident Response
Britvic	HR Security
Britvic	Compliance
Britvic	Web Security
Thwaites	HR Security
Thwaites	Compliance
Thwaites	Web Security
Thwaites	Incident Response
PepsiCo	HR Security
PepsiCo	Web Security
PepsiCo	Incident Response

Second table name: "Control Set Fact Table": 

Control Set Index	Control Set Name
1	HR Security
2	Compliance
3	Web Security
4	Incident Response

Desired outcome - missing data: 

Business Unit	Control Set
Fever Tree	Web Security
Britvic	Incident Response
PepsiCo	Compliance

[lbendlin]

Your "control set fact table"  likely isn't a fact table, it seems to be a reference/dimension table.

Consider using a data model

and a graphical solution to the question.

What should happen if a business unit misses more than one control set?

[AndrewPF]

I tried using the "Controls present" formula but I get the error message "A single value for column [column name] in table [table name] cannot be determined.  This can happen when a measure formula refers to a column that contains many values...". 

I probably should have specified that there are other columns in both tables.  Does that matter? 

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Ashish_Mathur]

Hi,

These measures work

Controls present = CONCATENATEX(VALUES(Company[Control Set]),Company[Control Set],",")

Controls absent = CONCATENATEX(EXCEPT(Control,VALUES(Company[Control Set])),Control[Control Set],",")

[lbendlin]

Read about the EXCEPT() function in DAX