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...
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



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!

edhans's avatar
edhans
Community Champion
6 years ago

It is interesting how fast DAX is. I thought I'd see if the SUMX with the FILTER was faster or slower than a LOOKUPVALUE.

 

Grabbed a million records and created another table unrelated and used the logic from LOOKUPVALUE that sanalytics used vs the SUMX/FILTER method I used.

 

LOOKUPVALUE - 5,031 milliseconds.

SUMX/FILTER - 4,771 milliseconds.

 

No one would notice that third of a second. It got interesting though when there was a relationship.

LOOKUPVALUE - 4,206 milliseconds

SUMX/FILTER - 1,946 milliseconds.

 

LOOKUPVALUE doesn't seem to take advantage of the relationships, whereas FILTER does. LOOKUPVALUE also relies heavily on the formula engine in DAX, which is slower than the storage engine. Makes sense as FILTER() is manipulating the table whereas a lookup is comparing against every field value.

 

Though that was interesting. On a model with 22,000 records, the difference would be imperceptable. As it grows. a lookup isn't the way to go. It is a very "excel like" function and not optimized for how DAX works it appears.