Forum Discussion

Ubedulla1988's avatar
Ubedulla1988
Regular Visitor
2 years ago
Solved

Need assistance with SWITCH function with positive and negative percentages.

Hi Team,
I have a requirement to show "increased", "decreased" and "remained flat at".
I have a measure which returns percentage, If the percentage is between -0.05 and 0.05 then "remained flat at".
If percentage is > 0.05 then "increased"
If percentage is > -0.05 then "increased". I am getting incorrect results even after applaing correct logic using SWITCH.
Please check the measure defination and suggest me.
My percenage measure values is 0.4 which is greate then 0.05, it should show "has increased by", but it is returing "has decreased by". Please check and suggest.


Need Help
  • Wilson_'s avatar
    Wilson_
    2 years ago

    Ubedulla1988,

     

    Read the edit in the message you responded to and see if that's what you're looking for.


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

     

    P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

7 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Remove all the FORMAT stuff, it will convert to string and you don't want that.

    It's easier to start with a simple IF to test these things so that you can get something simple to work before writing the whole thing.

    • Ubedulla1988's avatar
      Ubedulla1988
      Regular Visitor
      2 years ago

      Thanks for responding on issue.
      Even after removing Formate, still see incorrect results. If you look at the percentage, it is 0.4 which is bigger than 0.05 and It should return "has increased by". But it is returning "has remained flat at".

      • gmsamborn's avatar
        gmsamborn
        Icon for Super User rankSuper User
        2 years ago

        Hi Ubedulla1988 

         

        It looks like you are comparing a percentage with a basic number.

         

        .4% is actually equal to .004 which you are comparing to .05 (5%)

         

        Also, the line comparing _Check with "--" won't do anything since _Check is a number, not a string.

    • Ubedulla1988's avatar
      Ubedulla1988
      Regular Visitor
      2 years ago

      Please refer the below screenshot. Percentage is 1.1%, Still I could see "has remained flat at",  it supposed to be "has increased by".


      Thank you..

      • Wilson_'s avatar
        Wilson_
        Icon for Memorable Member rankMemorable Member
        2 years ago

        Hi Ubedulla1988,

         

        Again, as gmsamborn mentioned, you are incorrectly comparing a percentage with a decimal. 1.1% is not greater than 0.05 (aka 5%). 1.1% is greater than -0.05 (aka -5%) and is less than 0.05 (aka 5%) and therefore meets your second criteria. Therefore, it is correctly returning "has remained flat at" per your expected logic.

         

        EDIT: It sounds like you might actually be wanting 0.05% and -0.05%, which are 0.0005 and -0.0005 respectively.


        ----------------------------------
        If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

         

        P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.