Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

If function not working

Hello all,

 

I am new to Power BI and I am trying to write an IF function in a column. I have two different tables that I need to pull data from. THe column needs to display "If the catagory on the HR table says "PBL", then display the total budgeted salary from the Budget table, if anything else, display the [Annual Rate or max(Mid-Range Rate, Last Occupant Salary)] * [Funding Distribution]/100) from the HR table. But I am getting this error and cannot figure out why.

See screen shot.

 

Greg_Deckler

 

Thank you in advance

 

Need Help
  • sanalytics's avatar
    sanalytics
    6 years ago

    Hi Anonymous 
    As Greg_Deckler Suggested..
    you can try below formula..

    your col name=IF(HR[Category]="PBL",LOOKUPVALUE(Budget[Total Budgeted Base Salary],Budget[Combo Key],HR[Combo Key]),(HR[Annual Rate or max(Mid-Range Rate, Last Occupant Salary)]*HR[Funding Distribution]/100) )
     
    Hope it will solve your issue..

    Regards,
    snandy



23 Replies

  • sanalytics's avatar
    sanalytics
    Super User
    6 years ago

    Hi Anonymous 
    As Greg_Deckler Suggested..
    you can try below formula..

    your col name=IF(HR[Category]="PBL",LOOKUPVALUE(Budget[Total Budgeted Base Salary],Budget[Combo Key],HR[Combo Key]),(HR[Annual Rate or max(Mid-Range Rate, Last Occupant Salary)]*HR[Funding Distribution]/100) )
     
    Hope it will solve your issue..

    Regards,
    snandy



    • edhans's avatar
      edhans
      Community Champion
      6 years ago

      Theoritically this should work

       

      Test = 
SUMX(
    FILTER(
        ALL(Budget),
        Budget[Combo Key] = HR[Combo Key]
    ),
    Budget[Total Budgeted Base Salary]
)

       

      but I'm not getting any hits. Are you sure those combo keys are the same? there are over 20,000 combo keys. Can you give me one that you are 100% sure is in both tables so I can test?

       

      Oh, wait. I'm getting alot of hits, and a LOT of blanks. But that is working.

       

       

      So you could replace your Budget[whatever that column was] with the above SUMX function.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I just did this thanks to sanalytics 

         

        =IF(HR[Category]="PBL",LOOKUPVALUE(Budget[Total Budgeted Base Salary],Budget[Combo Key],HR[Combo Key]),(HR[Annual Rate or max(Mid-Range Rate, Last Occupant Salary)]*HR[Funding Distribution]/100) )

         

         

        And it works!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This worked! Thank you!

      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        If your budget table is not related to this table at all, you'll need to use the LOOKUPVALUE() function. The concept is simlilar to a VLOOKUP in Excel, but the syntax is way different.

         

        LOOKUPVALUE ( <Result_ColumnName>, <Search_ColumnName>, <Search_Value> [, <Search_ColumnName>, <Search_Value> [, … ] ] [, <Alternate_Result>] )
  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Because you are referencing the entire [Total Budgeted Base Salary] column in the Budget table. You'd need to modify that to say RELATED('Budget'[Total Budgeted Base Salary]) and that will only work if it is a 1-1 or 1-many relationship from the budget table to this table. If it is not, then you'll have to post some data so we can figure it out.

     

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly
    How to provide sample data in the Power BI Forum

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    Try wrapping Budget[Total Budgeted Base Salary] with a SUM or MAX or something. Looks like that is in a different table and perhaps no relationship?
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I tried wrapping it in a sum and it sums the entire column and gives this huge number, I just want it to return the value for one person not the sum of the whole column

       

      Greg_Deckler 

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        Right, well, we know where the issue is. Well, if edhans is already investigating then you are in good hands. I expect he will get you a solution that involves LOOKUPVALUE, etc.

  • sanalytics's avatar
    sanalytics
    Super User
    6 years ago

    Hello all,

    i am completly agree to edhans what he has tried to show.Sumx/Filter is much faster than LOOKUPVALUE.
    I also believe that, one formula can achieve by multiple ways.
    We the people who love to write Dax, some times we try to solve problem in own way that has been already solved.I have solved multiple problems which already Greg_Deckler  amitchandak edhans  Ashish_Mathur solved those problem before.Not looking for any kudos but definitely for learning to see how i can increase my skill.
    Thanks to  edhans for showing a beautiful difference between LOOKUPVALUE vs SUMX/FILTER.

     

    Regards,

    snandy 

    stay home,stay safe..

     

     

     

     



    • edhans's avatar
      edhans
      Community Champion
      6 years ago

      Thanks sanalytics - that is part of the fun with Power BI. It is like Excel - there are so many ways to arrive at the right answer, but fun trying to determine the best and fastest way.  Power on!