https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Lookupvalue-ignoring-null-result-and-returning-the-not-null/m-p/3416365#M129271 <P>Hi,<BR /><BR />I have this Table1 with ID's and a "Status" column:<BR />(Some ID's will repeat and I can't change this)<BR /><BR />Table1</P><TABLE><TBODY><TR><TD>ID</TD><TD>&nbsp;</TD><TD>Status</TD></TR><TR><TD>568253</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>714417</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>714417</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>349856</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>664270</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>595900</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>725439</TD><TD>&nbsp;</TD><TD>Em férias</TD></TR><TR><TD>725439</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>343945</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>343945</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>324091</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>324091</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>322606</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>612732</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>612732</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>304312</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>304312</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>304312</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>356269</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>356269</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>531853</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>531853</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>621330</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>473833</TD><TD>&nbsp;</TD><TD>Em férias</TD></TR><TR><TD>349112</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>349112</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>349112</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>23291</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>23291</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>23291</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>438195</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>438195</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>625815</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>703150</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>703150</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>351767</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>699483</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>699483</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>425060</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>425060</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>425060</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>410946</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>410946</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>410946</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR></TBODY></TABLE><P><BR />Then I have this Table2 with a column "ID" with the unique values from "ID" from Table1 and some other unique IDs, I would like to get the not-null "Status" from Table1 and return a "Not found" for "ID" with only null values in column Status, creating a new column in Table2 using DAX:<BR /><BR />Expected result:<BR />Table2</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>ID</TD><TD>&nbsp;</TD><TD>NewColumn</TD></TR><TR><TD>568253</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>714417</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>349856</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>752420</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>766404</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>664270</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>746061</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>736935</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>595900</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>725439</TD><TD>&nbsp;</TD><TD>Em férias</TD></TR><TR><TD>343945</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>324091</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>761841</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>762486</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>322606</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>612732</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>304312</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>356269</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>531853</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>621330</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>473833</TD><TD>&nbsp;</TD><TD>Em férias</TD></TR><TR><TD>349112</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>23291</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>438195</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>625815</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>703150</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>351767</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>699483</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>425060</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>410946</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR></TBODY></TABLE><P><BR />Appreciate your help,</P> Tue, 05 Sep 2023 19:52:41 GMT Anonymous 2023-09-05T19:52:41Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Lookupvalue-ignoring-null-result-and-returning-the-not-null/m-p/3416365#M129271 <P>Hi,<BR /><BR />I have this Table1 with ID's and a "Status" column:<BR />(Some ID's will repeat and I can't change this)<BR /><BR />Table1</P><TABLE><TBODY><TR><TD>ID</TD><TD>&nbsp;</TD><TD>Status</TD></TR><TR><TD>568253</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>714417</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>714417</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>349856</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>664270</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>595900</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>725439</TD><TD>&nbsp;</TD><TD>Em férias</TD></TR><TR><TD>725439</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>343945</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>343945</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>324091</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>324091</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>322606</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>612732</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>612732</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>304312</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>304312</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>304312</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>356269</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>356269</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>531853</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>531853</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>621330</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>473833</TD><TD>&nbsp;</TD><TD>Em férias</TD></TR><TR><TD>349112</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>349112</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>349112</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>23291</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>23291</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>23291</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>438195</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>438195</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>625815</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>703150</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>703150</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>351767</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>699483</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>699483</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>425060</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>425060</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>425060</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>410946</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>410946</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>410946</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR></TBODY></TABLE><P><BR />Then I have this Table2 with a column "ID" with the unique values from "ID" from Table1 and some other unique IDs, I would like to get the not-null "Status" from Table1 and return a "Not found" for "ID" with only null values in column Status, creating a new column in Table2 using DAX:<BR /><BR />Expected result:<BR />Table2</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>ID</TD><TD>&nbsp;</TD><TD>NewColumn</TD></TR><TR><TD>568253</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>714417</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>349856</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>752420</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>766404</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>664270</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>746061</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>736935</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>595900</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>725439</TD><TD>&nbsp;</TD><TD>Em férias</TD></TR><TR><TD>343945</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>324091</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>761841</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>762486</TD><TD>&nbsp;</TD><TD>NOT FOUND</TD></TR><TR><TD>322606</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>612732</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>304312</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>356269</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>531853</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>621330</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>473833</TD><TD>&nbsp;</TD><TD>Em férias</TD></TR><TR><TD>349112</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>23291</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>438195</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>625815</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR><TR><TD>703150</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>351767</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>699483</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>425060</TD><TD>&nbsp;</TD><TD>Programada</TD></TR><TR><TD>410946</TD><TD>&nbsp;</TD><TD>Já realizada</TD></TR></TBODY></TABLE><P><BR />Appreciate your help,</P> Tue, 05 Sep 2023 19:52:41 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Lookupvalue-ignoring-null-result-and-returning-the-not-null/m-p/3416365#M129271 Anonymous 2023-09-05T19:52:41Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Lookupvalue-ignoring-null-result-and-returning-the-not-null/m-p/3416407#M129276 <P>Hi&nbsp;Anonymous</LI-USER>&nbsp;</P> <P>Why 425060 is not found? Same for 410846 and perhaps others.&nbsp;<BR />Also do tou have a relationship between the two tables?</P> Tue, 05 Sep 2023 19:44:05 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Lookupvalue-ignoring-null-result-and-returning-the-not-null/m-p/3416407#M129276 tamerj1 2023-09-05T19:44:05Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Lookupvalue-ignoring-null-result-and-returning-the-not-null/m-p/3416431#M129278 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="317289" data-lia-user-login="tamerj1" class="lia-mention lia-mention-user">tamerj1</a>&nbsp;, sorry, I have now adjusted my question table.&nbsp;<BR />I do not have any relationship set, but I could configure&nbsp;<BR />Many (table1) &lt;---- to one (table2) by using the "ID" column on each other&nbsp;</P> Tue, 05 Sep 2023 19:55:48 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Lookupvalue-ignoring-null-result-and-returning-the-not-null/m-p/3416431#M129278 Anonymous 2023-09-05T19:55:48Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Lookupvalue-ignoring-null-result-and-returning-the-not-null/m-p/3416454#M129279 <P>Anonymous</a>&nbsp;</P> <P>If you do so then you can simply do</P> <P>New Column =<BR />MAAX (</P> <P>RELATEDTABLE ( tabl1 ),</P> <P>table1[Satus]&nbsp;</P> <P>)</P> <P>&nbsp;</P> Tue, 05 Sep 2023 20:23:38 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Lookupvalue-ignoring-null-result-and-returning-the-not-null/m-p/3416454#M129279 tamerj1 2023-09-05T20:23:38Z