missing data but with additional information from both tables

Question

If I have a list of standard controls as follows:&nbsp;&nbsp;Control SetHR SecurityComplianceWeb Security&nbsp;Incident Response&nbsp;&nbsp;and a separate list of businesses, along with their own controls:&nbsp;&nbsp;&nbsp;Company&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Control SetFever Tree&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HR Security&nbsp;Fever Tree&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Compliance&nbsp;Fever Tree&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Incident ResponseBritvic&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HR SecurityBritvic&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ComplianceBritvic&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Web SecurityThwaites&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HR SecurityThwaites&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Compliance&nbsp;Thwaites&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Web&nbsp;SecurityThwaites&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Incident Response&nbsp;PepsiCo&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HR Security&nbsp;PepsiCo&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Web Security&nbsp;PepsiCo&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Incident Response&nbsp;&nbsp;then I need a way of identifiying which business have missing controls, and which controls are missing from each business, i.e.:&nbsp;&nbsp;Company&nbsp; &nbsp; &nbsp; &nbsp;Control Set&nbsp;Fever Tree&nbsp; &nbsp; &nbsp; &nbsp;Web Security&nbsp;Britvic&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Incident Response&nbsp;PepsiCo&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Compliance &nbsp;Any ideas please?&nbsp;

ashish_mathur · Answer

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],",")

&nbsp;

lbendlin · Answer

Read about the EXCEPT() function in DAX

andrewpf · Answer

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 · Answer

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

andrewpf · Answer

First table name: "Control Sets":&nbsp;&nbsp;Business UnitNameFever TreeHR Security&nbsp;Fever TreeCompliance&nbsp;Fever TreeIncident ResponseBritvicHR SecurityBritvicComplianceBritvicWeb SecurityThwaitesHR SecurityThwaitesCompliance&nbsp;ThwaitesWeb SecurityThwaitesIncident Response&nbsp;PepsiCoHR Security&nbsp;PepsiCoWeb Security&nbsp;PepsiCoIncident Response&nbsp;&nbsp;Second table name: "Control Set Fact Table":&nbsp;&nbsp;Control Set IndexControl Set Name1HR Security2Compliance&nbsp;3Web Security4Incident Response&nbsp;&nbsp;Desired outcome - missing data:&nbsp;&nbsp;Business UnitControl Set&nbsp;Fever TreeWeb Security&nbsp;BritvicIncident Response&nbsp;PepsiCoCompliance&nbsp;

Forum Discussion

missing data but with additional information from both tables

8 Replies

Related content

Missing information

missing data

missing data

Generating Missing Data

Inferring Missing Columns and Imputing Missing Data

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

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

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