Convert if statement to value

[Anonymous]

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  

[v-kkf-msft · Accepted solution]

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.

[Anonymous]

Thanks a million

[VahidDM]

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/

[ValtteriN]

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!

[Anonymous]

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.