LOOKUPVALUE : "a table wuith multiple value was supplied where a single value was expected

[admin_xlsior]

Hi Guys,

So I have this master table (Rate table)

Year	Region	Branch	Target rate
2018	A	098	80.00 %
2018	A	099	80.00 %
2018	B	100	80.00 %
2019	A	098	95.00 %
2019	A	099	95.00 %
2019	B	100	95.00 %

I intent to use a lookup based on the Year and Branch, to get my rate for my Fact table. 

So I create this measure :

Target rate related = LOOKUPVALUE('Target'[Target rate], 'Target'[Branch],DISTINCT('Region Branch'[Branch]), 'Target'[Year], VALUES('Date'[Year]))

The table RegionBranch is my Dimension Table connected to my FactTable, so in that formula, we didin't see my fact table it self. I supposed this is normal, right ?

However the measure I created, returns error :  "Calculation error in measure Target [Target  rate related]; A table with multiple value was supplied where a single value was expected"

Can advice on which data it says multiple value ? Coz, in my target, I'm positive if it filtered based on Year, there will be only one branch.

Thanks in advance.

[Zubair_Muhammad]

@admin_xlsior 

Try replacing VALUES with MAX

Target rate related =
LOOKUPVALUE (
    'Target'[Target rate],
    'Target'[Branch], MAX ( 'Region Branch'[Branch] ),
    'Target'[Year], MAX ( 'Date'[Year] )
)

[admin_xlsior]

To perry,

The diagram is something like below :

It has some duplication on Region, but for some reason we need that, and it's not the issue here 🙂

But for the branch, I can assured there is only one per year on my target table.

To Zubair,

Thanks, it's worked... Although I don;t understand why DISTINCT or VALUES not applied here ? I thought it will also make sure it elliminate duplicate ? DISTINCT with blank and VALUES with no blank, right ? 

By using MAX, how it's relate with Branch or year ? that function is to return the maximum of a value right ?

Kindly enlighten me 🙂

Thanks,

[Zubair_Muhammad]

@admin_xlsior 

VALUES function causes problem at the total row/Column. Because at the Total Row/Column, we have multiple values so it throws that error.

That's why we should use alternatives like Max/Min/SelectedValue etc

[admin_xlsior]

H Zubair,

Many thanks for your explanation.

Mind if I extend a bit since now I'm using MAX function. And the interesting part is, if somehow in my report, I don't display Branch, why it is shows the minimum of my Rate percentage value ? Actually I'm hoping it will be average, but before I do anything with my measure, currently it show the minimum value (while we using MAX instead)

My measure now is same like your advice (and it works for the lookup when I display Branch)

Target Work rate = LOOKUPVALUE('Target'[Target rate], 'Target'[Branch],MAX('Region Branch'[Branch]), 'Target'[Year], MAX('Date'[Year]))

I've changed my Target table value to be like like this :

Year	Region	Branch	Target rate
2018	A	098	80.00 %
2018	A	099	80.00 %
2018	B	100	65.00 %
2018	B	102	70.00 %
2019	A	098	95.00 %
2019	A	099	95.00 %
2019	B	100	80.00 %
2019	B	102	80.00 %

Currently it shows 65% in the year of 2018, while branch not in the report.

Thanks in advance,

[Zubair_Muhammad]

@admin_xlsior 

Without the Branch Field, it should take the Branch 102 i.e. max of all brannches for 2018 and return the value 70%

Year	Region	Branch	Target rate
2018	A	098	80.00 %
2018	A	099	80.00 %
2018	B	100	65.00 %
2018	B	102	70.00 %
2019	A	098	95.00 %
2019	A	099	95.00 %
2019	B	100	80.00 %
2019	B	102	80.00 %

[admin_xlsior]

Hi Zubair,

Yes, that was my 1st thought as well, but the actual is, it did shows the minimum value instead, which is I understand it is weird. 

Anyhow, is it possible for the measure to show the average of it? So with my data ->

Year	Region	Branch	Target rate
2018	A	098	80.00 %
2018	A	099	80.00 %
2018	B	100	65.00 %
2018	B	102	70.00 %

In average it will shows 73.75%

Thanks,

[Zubair_Muhammad · Accepted solution]

@admin_xlsior 

Could you share your file?

Give this measure a shot

Target Work rate =
VAR myyear =
    MAX ( 'Date'[Year] )
VAR mybranch =
    VALUES ( 'Region Branch'[Branch] )
RETURN
    CALCULATE (
        AVERAGE ( 'Target'[Target rate] ),
        'Target'[Branch] IN mybranch,
        'Target'[Year] = myyear
    )

[admin_xlsior]

Hi Zubair,

It worked. Many thanks for your help. 🙂

[parry2k]

@admin_xlsior can you share relationship diagram and in which table you want rate from rate table.