topic Return the values from a column that satisfy the conditions based on another columns in DAX Commands and Tips

Return the values from a column that satisfy the conditions based on another columns

Anonymous — Tue, 13 Apr 2021 22:46:24 GMT

Let's say that I have a table which contains 4 columns - participant name, fiscalyear, email, shareinfo. I want to display the email of only those participants who want to share their info and fiscalyear equal to current year.

I can do it easily in sql using case statement - CASE WHEN (shareinfo=1 and FiscalYear = YEAR(GETDATE())) THEN email END AS Email.

I tried using similar approach in powerbi using "if" statement but it is giving me error - "DAX comparison operations do not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

I'm writing this query - if(Query1[shareinfo]=true() & Query1[FiscalYear]=YEAR(TODAY()),Query1[email],"null")

Is there any other to achieve the desired result?

Re: Return the values from a column that satisfy the conditions based on another columns

Jihwan_Kim — Wed, 14 Apr 2021 03:05:46 GMT

Hi, Anonymous

Please correct me if I wrongly understood your question.

Please try the below.

query =
IF (
Query1[shareinfo] = TRUE ()
&& Query1[FiscalYear] = YEAR ( TODAY () ),
Query1[email],
"null"
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Re: Return the values from a column that satisfy the conditions based on another columns

Anonymous — Wed, 14 Apr 2021 03:39:42 GMT

Jihwan_Kim Thank you for your response! This doesn't seem to work since shareinfo and fiscalyear are of different data types. Shareinfo is of true/false data type and fiscalyear of whole number. I tried creating new columns by converting these two columns into text data type, but it still gives me the same error - "DAX comparison operations do not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values"