locked
Value from the first row in a filtered table? RRS feed

  • Question

  • Hello

    After 4 hours I couldn't find how to solve a problem, which seems simple, but with dax seems impossible to solve.

    I have an "Address" table with a "zip" and "city" columns, some addresses are missing the city, so there is an additional table "ZipCity", where there are also "zip" and "city" columns. The problem is, you can't have a relation between "Address" and "ZipCity" because the zip is not unique, the same zip can be used for two and more cities.

    Now I can get the city name from the first row in the "ZipCity" table with the same zip in the "ZipCity" table?

    With excel the vlookup function would solve the problem, because it returns only the first found row, but the lookupvalue in dax throws an exception if multiple rows are found.

    How can I transform this simple excel function in a dax function?

    vlookup(Address[zip],ZipCity,ZipCity[city])
    Thanks

    Tuesday, January 27, 2015 3:14 PM

Answers

  • well, usually the DAX function LOOKUPVALUE replaces Excels VLOOKUP
    however, in your case the results are not unique - a Zip-Code may have several cities associated with it
    DAX is more strict here compared to Excel which just returns the first match it finds

    to accomplish this you need to create the following calculated column in your Address-table:

    =CALCULATE(
    FIRSTNONBLANK('ZipCity'[City]; 1),
    FILTER(
        'ZipCity',
        'ZipCity'[Zip] = 'Address'[Zip])
    )

    this will give you the name of the first city that matches the zip in ALPHABETICAL ORDER

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Wednesday, January 28, 2015 8:13 AM
    Answerer
  • Thank for the reply, after fighting another 4 hours I got something working:

    =CALCULATE(DISTINCT(ZipCity[city]),TOPN(1,FILTER(ZipCity,ZipCity[zip]=Address[zip]),ZipCity[city],1))
    The first time that I tried with the FIRSTNOBLANK it didn't work, I can try again, it seems to be simpler.

    Wednesday, January 28, 2015 10:52 AM

All replies

  • well, usually the DAX function LOOKUPVALUE replaces Excels VLOOKUP
    however, in your case the results are not unique - a Zip-Code may have several cities associated with it
    DAX is more strict here compared to Excel which just returns the first match it finds

    to accomplish this you need to create the following calculated column in your Address-table:

    =CALCULATE(
    FIRSTNONBLANK('ZipCity'[City]; 1),
    FILTER(
        'ZipCity',
        'ZipCity'[Zip] = 'Address'[Zip])
    )

    this will give you the name of the first city that matches the zip in ALPHABETICAL ORDER

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Wednesday, January 28, 2015 8:13 AM
    Answerer
  • Thank for the reply, after fighting another 4 hours I got something working:

    =CALCULATE(DISTINCT(ZipCity[city]),TOPN(1,FILTER(ZipCity,ZipCity[zip]=Address[zip]),ZipCity[city],1))
    The first time that I tried with the FIRSTNOBLANK it didn't work, I can try again, it seems to be simpler.

    Wednesday, January 28, 2015 10:52 AM