topic Re: Insert value based on different columns in DAX Commands and Tips

Insert value based on different columns

joep78 — Fri, 20 Sep 2019 10:07:29 GMT

Hi there,

I’m relatively new using PBI and hopefully somebody can help me figuring out how to write a DAX statement.

My table name is ‘AnswerLine’ and within this table I’ve got a question column (Question) and an answer column (Answer)

I want to do a measure that when a combination of different answers is set on Y, a column (value) will get a value. If the answer is N, there is no action needed.

Example:

Question “ONG000.04” = Y & “ONG000.05” = Y then Value = 2

I’ve got 9 different combinations and in total we have 23 different questions.

Not sure if anybody can help me out with this input. If more input is needed, please let me know!

Thanks in advance for response!

Re: Insert value based on different columns

Anonymous — Fri, 20 Sep 2019 14:42:56 GMT

Adding a conditional column in the query editor perhaps?

https://support.office.com/en-us/article/add-a-conditional-column-power-query-f2422ed1-f565-4e64-ba3f-4f8d4616254e

If this works for you, please mark my answer as sovled. Cheers

Re: Insert value based on different columns

natabird3 — Mon, 23 Sep 2019 06:44:28 GMT

@joep78

Maybe try this primitive solution:

Measure = IF(CONTAINS(AnswerLine,AnswerLine[Answer],"Y"),1,0)

Measure 2 = COUNTX(AnswerLine,"Y")

Re: Insert value based on different columns

joep78 — Mon, 23 Sep 2019 11:01:53 GMT

Thanks for your reply, the following solutions based on your answer works for me:

Measure= IF(
AND(
OR(
CONTAINS(BaseORderAnswerLine;BaseORderAnswerLine[answer];"Ja";BaseORderAnswerLine[Question];"ONG000.04");
CONTAINS(BaseORderAnswerLine;BaseORderAnswerLine[answer];"Ja";BaseORderAnswerLine[Question];"ONG000.05"));
CONTAINS(BaseORderAnswerLine;BaseORderAnswerLine[answer];"Ja";BaseORderAnswerLine[Question];"ONG020")
)
;1;0)

with this, I have the right meausure output!