topic Re: Convert if statement to value in DAX Commands and Tips

Convert if statement to value

Anonymous — Fri, 21 Jan 2022 10:16:20 GMT

Hello,

Is tehere a way how to convert returned value from if statement into number?

In IMG (Column 2G_5RDC-TA) is visible that power bi does not sum returned value and treat them as string.

My formula is:

2G_5RDC-TA =

IF("5RDC" IN VALUES(Load_data_merged[Sufix_template.FDP]),

IF("TA" IN VALUES(Load_data_merged[Sufix_template.FDP]),1,""))

and format is whole number. I am about to use value for converting, but need your help.

Thank you

Re: Convert if statement to value

ValtteriN — Fri, 21 Jan 2022 14:37:21 GMT

Hi,

If you are referring to the total column the problem is not actually with aggregation. Total row uses the same logic as the column it counts the total from so in this case your total row tests what this dax returns:

2G_5RDC-TA =

IF("5RDC" IN VALUES(Load_data_merged[Sufix_template.FDP]),

IF("TA" IN VALUES(Load_data_merged[Sufix_template.FDP]),1,""))

Since the result is 1 the total is 1. To circumvent this you can use IF(SELECTEDVALUE(table[load_number])=blank(),CALCULATE([2G_5RDC-TA],all(table)),[2G_5RDC-TA]))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Convert if statement to value

Anonymous — Fri, 21 Jan 2022 14:56:57 GMT

Thank you ValtteriN,

Unfortunately this workaround does not helped me.

Yes, you are right. I am referncing to total. In your solution is refrence to loadnumber. Hovewer this field is never empty.

Re: Convert if statement to value

VahidDM — Sun, 23 Jan 2022 22:11:30 GMT

Hi Anonymous

It's a total issue, very common. See this link to find out why it's wrong and how to fix it:

https://www.vahiddm.com/post/why-my-measure-returns-the-wrong-total

You can change "" to blank() in your formula.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Convert if statement to value

v-kkf-msft — Tue, 25 Jan 2022 06:59:08 GMT

Hi Anonymous ,

Please try the following formula:

2G_5RDC-TA = SUMX ( SUMMARIZE ( Load_data_merged, Load_data_merged[Load number], "result", IF ( "5RDC" IN VALUES ( Load_data_merged[Sufix_template.FDP] ), IF ( "TA" IN VALUES ( Load_data_merged[Sufix_template.FDP] ), 1, "" ) ) ), [result] )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Convert if statement to value

Anonymous — Tue, 25 Jan 2022 09:09:55 GMT

Thanks a million