topic DAX to compare data in different tables and return a response in DAX Commands and Tips

DAX to compare data in different tables and return a response

Anonymous — Tue, 03 Sep 2024 15:44:04 GMT

I have a data table that contains responses from a set of audit questions which have been answered multiple times by different users with yes/no answers. I then have a data table that has the expected correct answer for each question (examples below)

I want to be able to create a column in the audit data table that compares the response against the "expected response" and then returns either a true or false result dependent on if it matches.

I am struggling to write the DAX for this can anyone help?

AUDIT DATA

question	audit response
have all staff members had training	yes
have all volunteers had training	no
are all doors fitted with external locks	no
have all staff members had training	no
have all volunteers had training	yes

Expected responses

question	expected response
have all staff members had training	yes
have all volunteers had training	yes
are all doors fitted with external locks	yes

Re: DAX to compare data in different tables and return a response

BabyYoda — Tue, 03 Sep 2024 16:02:42 GMT

Response Matches Expected Response = Var GetExpectedResponse = LOOKUPVALUE('Expected Response'[Expected Response], 'Expected Response'[Question],'Audit Data'[Question])

RETURN IF(GetExpectedResponse='Audit Data'[Audit Response], TRUE(), FALSE())

Re: DAX to compare data in different tables and return a response

etane — Tue, 03 Sep 2024 16:55:09 GMT

I wouldn't solve this with DAX. I would join the two tables by the Question field.

Then, in Table, bring over Responder Name, Question and Audit Response from Audit table and Expected response from the Expected Response table.

Re: DAX to compare data in different tables and return a response

Anonymous — Wed, 04 Sep 2024 09:38:41 GMT

thank you that works perfectly