LOOKUP does not work

[Anonymous]

I have a huge main table called 'DVENDAS';

I have a new table using SUMMARIZECOLUMNS, that I called 'FS.0';

so I have a second new table using SUMMARIZECOLUMNS and FILTER again, that I called 'FS.2';

and I also have a problem:

I must create a new column in 'FS.2' using LOOKUPVALUE searching for values in 'FS.0' but I can't, this formula does not work. I got the following error: "A table of multiple values was supplied where a single value was expected" but it does not make sense because this table is summarized. The value showed is #ERROR.

I have used this formulas: 

Agência Venda = LOOKUPVALUE(
    'FS.0'[Agência];
    'FS.0'[Linha];[Linha];
    'FS.0'[Origem];[Origem];
    'FS.0'[Destino];[Destino];
    'FS.0'[Hora];[Hora];
    'FS.0'[Serviço];[Serviço];
    'FS.0'[Passageiro];[Passageiro];
    'FS.0'[Doc];[Doc];
    'FS.0'[Data Servi.];[Data Servi.];
    'FS.0'[Preço];[Preço];
    'FS.0'[Poltrona];[Poltrona];
    'FS.0'[Empresa];[Empresa];
    'FS.0'[Tipo Venda]; "VENDA DE RESERVA";
    'FS.0'[Status]; "VENDIDO")

OR

Agência V = 
CALCULATE (
    FIRSTNONBLANK( 'FS.0'[Agência] ; 1 );
    FILTER (
        'FS.0';
        'FS.0'[Data Servi.] = [Data Servi.]
        && 'FS.0'[Destino] = [Destino]
        && 'FS.0'[Doc] = [Doc]
        && 'FS.0'[Empresa] = [Empresa]
        && 'FS.0'[Hora] = [Hora]
        && 'FS.0'[Logins.nome] = [Logins.nome]
        && 'FS.0'[Origem] = [Origem]
        && 'FS.0'[Passageiro] = [Passageiro]
        && 'FS.0'[Poltrona] = [Poltrona]
        && 'FS.0'[Preço] = [Preço]
        && 'FS.0'[Serviço] = [Serviço]
        && 'FS.0'[Status] = "VENDIDO"
    )
)

As you can see I tried the FIRSTNONBLANK formula but unsuccessfully... I got a single value for all rows in this way above, seems like FILTER is not working as I expected, maybe I am missing something.

How can I search for a conditional value in this case? Please help.

[v-danhe-msft]

Hi @Anonymous,

Based on my test, the LOOKUPVALUE Function (DAX) has three parameters, the result is filtered by the third parameter  to match the second parameter, you could refer to below demo:

Row table(TableA):

SUMMARIZECOLUMNS TableB:

TableB = SUMMARIZECOLUMNS('TableA'[ID],'TableA'[Sales]) 

SUMMARIZECOLUMNS TableC:

TableC = SUMMARIZECOLUMNS('TableA'[ID])

Lookupvalue column:

LookupvalueColumn = LOOKUPVALUE(TableB[Sales],TableB[ID],'TableC'[ID])

Result:

I suggest you test again.

Regards,

Daniel He

[Anonymous]

Thanks for reply @v-danhe-msft,

It is exactly what I'm doing, or not? 

Agência Venda = LOOKUPVALUE(
    'FS.0'[Agência];
    'FS.0'[Linha];[Linha];
    'FS.0'[Origem];[Origem];
    'FS.0'[Destino];[Destino];
    'FS.0'[Hora];[Hora];
    'FS.0'[Serviço];[Serviço];
    'FS.0'[Passageiro];[Passageiro];
    'FS.0'[Doc];[Doc];
    'FS.0'[Data Servi.];[Data Servi.];
    'FS.0'[Preço];[Preço];
    'FS.0'[Poltrona];[Poltrona];
    'FS.0'[Empresa];[Empresa];
    'FS.0'[Tipo Venda]; "VENDA DE RESERVA";
    'FS.0'[Status]; "VENDIDO")

But in my case there is many parameters, the 3rd parameter must to match the 2nd; the 5th must match the 4th; ...; the 27th must match the 26th.

[v-danhe-msft]

Hi @Anonymous,

It could work on my side, could you please share some sample file to have a tets if possible?

Regards,

Daniel He

[Anonymous]

@v-danhe-msft

Yeah, I know! There is a table in same workspace called 'PTA' that I use LOOKUPVALUE in this way and it is working fine.

But in this specific case is not... Maybe because I am using with a summarized table? There is some specific event like that wich may cause this? I don't know.

I am not able to share my workspace because it is huge insane! 'DVENDAS' has more than 8 millions of rows, 'FS.0' +65.000 and 'FS.2' +37.000.

Do you know an alternative way to reach this result like LOOKUPVALUE? Like FIRSTNONBLANK but with filter?

Or someone knows what could be wrong here?