Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

LOOKUP does not work

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.

4 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee

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

      Thanks for reply v-danhe-msft,

       

       

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

       

      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's avatar
        v-danhe-msft
        Microsoft Employee

        Hi Anonymous,

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

         

        Regards,

        Daniel He