Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Convert if statement to value

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's avatar
    v-kkf-msft
    4 years ago

    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.

     

5 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

       

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks a million