Forum Discussion
If function not working
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
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.
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!
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.