Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Lookupvalue returns nonsense value

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 disti...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

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