Lookupvalue returns nonsense value

[Anonymous]

Hello all,

I have this table with multiple same entries. I can't just delete the multiple entries cause I need to count them for a visual later.

Anyway, I also needed their a table with their distinct count. So I made a distincttable using:

Distincttable = DISTINCT(SELECTCOLUMNS('UNION',"Sales Doc.",'UNION'[Sales Doc.]))

--> UNION in this case is not a function, but the name of the table with the duplicated etc. entries

However,  I now need to assign the Net Value to the Sales Doc. Numbers in my distincttable for some visuals to work properly.  Both values are present in the UNION-table, just more than one time.

I did this by adding a column to the distincttable with the formula:

Net Value = LOOKUPVALUE('UNION'[Net value], 'UNION'[Sales Doc.],'Distincttable'[Sales Doc.],0)

--> for those not familiar with the formula: This formula searches the Sales Doc numbers contained in my distincttable in the Union-table, and, if found, picks out value from the Net Value column in the same row and transfers it back to the Net Value-column in my Distincttable. 0 indicates the Sales doc. number from the distincttable has not been found in the UNION-table. 

Now my problem: PowerBI assigned about half the numbers a 0, saying the value has not been found in the UNION-table.

This, however, is not possible, cause as you can see in the formula that I used to create the distincttable:Distincttable = DISTINCT(SELECTCOLUMNS('UNION',"Sales Doc.",'UNION'[Sales Doc.])) 

I literally got the Sales Doc. numbers from there. So why does PowerBI not find the Sales Doc. Numbers in the table it had literally extracted them from? I also refreshed the whole thing- still the same outcome.

EDIT: I considered that the Net Value could just be 0, however I looked it up in the raw table and nope, it was not 0.

[Anonymous · Accepted solution]

Hi @Anonymous ,

LOOKUPVALUE() function could not return more than one distinct value. As @Greg_Deckler suggested, you could use MAXX() instead. Or SUMX(), FIRSTNONBLANK() and so on like this:

Distincttable =
ADDCOLUMNS (
    DISTINCT ( 'UNION'[Sales Doc.] ),
    "MAX Net Value",
        MAXX (
            FILTER ( 'UNION', 'UNION'[Sales Doc.] = EARLIER ( 'UNION'[Sales Doc.] ) ),
            [Net value]
        ),
    "SUM Net Value",
        SUMX (
            FILTER ( 'UNION', 'UNION'[Sales Doc.] = EARLIER ( 'UNION'[Sales Doc.] ) ),
            [Net value]
        ),
    "FIRST Net Value", FIRSTNONBLANK ( 'UNION'[Net value], 1 )
)

 The final output is shown below:

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Anonymous Evelyn for the great support!

I had to play around with the expression a little but it looked like it worked...

I mean it does a good job and picks all the Sales Docs.& it also looks like it retrieves the correspondign Net Values but unfortuntely not all as it seems... I aggregated the values manually in the raw tables and the number PowerBI gives me is roughly 4 times the number it should be and I have absolutely no idea why that is...!

And yeah, I double-checked my measures as well as the Excel-functions I used to calculate by hand...

[Greg_Deckler]

@Anonymous - It's difficult to say honestly because I don't feel like I really understand your data, data model, etc. If you could potentially post just a bit of sample data as text, expected output and basically explain how to get from a to b that would be super helpful. 

Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Greg_Deckler]

@Anonymous Try using the MAXX(FILTER(...),...) method instead of LOOKUPVALUE. I have seen LOOKUPVALUE return bizarre results in various situations but MAXX(FILTER(...),...) never fails.

[Anonymous]

Thanks for the tip Greg!

So I tried using:

Net Value = MAXX(FILTER('Distincttable', Distincttable[Sales Doc.]), 'UNION'[Net Value]) but the error returned that a single value for "Net Value" in 'UNION' couldn't be determined...

What does that mean for me/ what do I have to change?

I mean I have to assign ~170k Documents a Value so...

ALso yeah, the LookUpValue does indeed make weird things... before I applied it to my real dataset, where it failed now, I applied it to a DummyDataset where it worked just fine...