If function not working

[Anonymous]

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

[sanalytics]

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]

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!

[sanalytics · Accepted solution]

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]

This worked! Thank you!

[edhans]

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]

one example of a combo key I know for sure is in both tables is 

EE238847_00067707_D223730_F110_C10650

[Anonymous]

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]

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.

[Greg_Deckler]

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]

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 

[AilleryO]

Hi,

Not so sure about your what your model is.

But I'm quite sure your solution is in this video :

https://www.sqlbi.com/tv/different-types-of-many-to-many-relationships-in-power-bi/

Hope it helps,

[Greg_Deckler]

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.

[edhans]

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

[Anonymous]

When I try to to the "Related" function it does not give me the option to select anything from the budget table. How can I post the file for you to look at?

[edhans]

Drop it in dropbox, onedrive, etc.

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

[Anonymous]

@edhans 

https://www.dropbox.com/s/orl77vjmgugkqki/FTE%20Budgeting.pbix?dl=0

[edhans]

Thanks. Investigating now....

[edhans]

@Anonymous This is a subset of your model for just the HR and Budget tables.

What are you doing in the HR table, and how would you expect it to know which record(s) to get from the Budget table?

You cannot use RELATED with this kind of relationship. Not even sure if you can use RELATEDTABLE, though I'll have to investigate more.

[Anonymous]

We have a unique 'Combo Key' in each table, I thought that it would be able to reference that

[AilleryO]

Hi,

To send us a model, use the insert Attachments link you should have when you post.