Dax formulas work and no work

[Anonymous]

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!!

[Anonymous]

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]

@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]

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!!

[Anonymous]

@Anonymous - The calculation depends on what you're trying to achieve. Could you provide some sample data and expected result. Especially important is what do you want to happen when there is more than one value row with the same employee / manager?

[Anonymous]

I think i understandthe problem. When there are 2 months in the power bi, there are 2 different results, thats why figures ERROR. When I only leave one month, this doens´t happen.

Is there a way I can include in the formulas I already have a filter that is sincronized with my slicer Month?

thanks a lot again @Anonymous !!

[v-lili6-msft]

HI, @Anonymous 

The best way is that add a year-month column (the next month) when you load the next month data.

Then add a conditional that group year-month.

Best Regards,

Lin

[Anonymous]

@Anonymous - If you want to continue to use LOOKUPVALUE, you could add a third filter for the month.

However, it seems like you already have the information you need - I am not sure whether you need this calculation at all. For instance, if you are using a table or matrix, simply use the existing column.