Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dax formulas work and no work

Hi! 

I have a weird problem with my data and dax formulas.

I have an excel worksheet where everymonth I paste the data from the past month.

I've load this excel to my power bi, create some DAX formulas that work ok. These are 2 examples:

1) Estado Manager = LOOKUPVALUE('PERU Aplausometro - Enviados Gr'[Estado],'PERU Aplausometro - Enviados Gr'[Nombre de usuario],('PERU Aplausometro - Enviados Gr'[Manager Login ID Nombre de usuario]))

2) Rec. Otorgados Manager = LOOKUPVALUE('PERU Aplausometro - Enviados Gr'[Cantidad de reconocimientos otorgados],'PERU Aplausometro - Enviados Gr'[Nombre de usuario],('PERU Aplausometro - Enviados Gr'[Manager Login ID Nombre de usuario]))

 

When I load the next month data (march), this 2 formulas don't work anymore. The errors that appear are the followings: "a table of multiple values was supplied where a single value was expected".

If I erase what I just load, the dax formulas work fine again.

 

In the excel i tried print the format of the past month to the new one, but still doesn´t work.

 

Anybody knows why is this happening??

Thanks a lot!!

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Also, if i just paste part of my new data in my excel and refresh, only the second dax formula (Rec. Otorgados Manager) doesn´t work. The first one does!

     

    thanks!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous -

      The function LOOKUPVALUE only can return a single value (Scalar).

      If more than one value meets the criteria that you provide, that will cause an error.

       

      In your first example, if there is more than 1 [estado] associated with a user/manager, the calculation will fail.

      In your second example, if there is more than 1  [Cantidad de reconocimientos otorgados] associated with a user/manager, the calculation will fail.

       

      You either need to provide more filters, or use a different calculation.

       

      Hope this helps,

      Nathan

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I just understand the difference why in one works and in the other one don't.

        I have the data of all the employees (including managers and no managers) with columns such as the quantity of recognitions they gave and the name of his manager.

        I create another column beside the managers name with the "Estado/Status" (first formula) and how many recognitions they gave (the second i used). Which other formula would you recommend? 

        thanks!!