Forum Discussion
Lookupvalue in measure
Hello guys,
Maybe you can solve the problem.
I whant to make a measure, that counts - [Overdue days]*([Overdue Debt]*[Ratio])
[Overdue days] and [Overdue Debt] I have measure, so everything is fine.
A probelm is with [Ratio] - this is a field in other table. It should bring just one value.
If it will be a column there will be a formula, like that
Lookupvalue(Client[Ratio]; Client[No_]; Client LE[Source No_]), but in measure is doesn't work.
Tables Client and Client LE has a relationship [No_] =[Source No_]
Is there any DAX measure that I can use to bring some value in my calculations?
Hi there,
I think the problem might be that you have many "Client LE[Source No_]" at some point in the context that you are in, which results in error. When you use the LOOKUPVALUE funciton you must ensure that you only have one value (scalar) in "Client LE[Source No_]".
I think you can do two things, depending on what you are trying to do:
1. in you Ratio function you would wrap the LOOKUPVALUE funciton with a IF statement that ensures that you only have one value in "Client LE[Source No_]" , i.e. IF( HASONEVALUE( Client LE[Source No_] ) ; Lookupvalue(Client[Ratio]; Client[No_]; VALUES(Client LE[Source No_])) )
2. second option would be to use an iteratior, something like:
SUMX( // can also be AVERAGEX, or other X function
ADDCOLUMNS(
SUMMERIZE(
Client;
Client[No_];
Client[Ratio];
"Days"; [Overdue Days];
"Debt"; [Overdue Debt]
);
"result";[Days]*([Debt]*Client[Ratio])
);
[result]
)
I have not tried this and this might include some small syntax error, but I hope this helps to achive what you are trying to do.Regards,
Kristjan76
2 Replies
Hi there,
I think the problem might be that you have many "Client LE[Source No_]" at some point in the context that you are in, which results in error. When you use the LOOKUPVALUE funciton you must ensure that you only have one value (scalar) in "Client LE[Source No_]".
I think you can do two things, depending on what you are trying to do:
1. in you Ratio function you would wrap the LOOKUPVALUE funciton with a IF statement that ensures that you only have one value in "Client LE[Source No_]" , i.e. IF( HASONEVALUE( Client LE[Source No_] ) ; Lookupvalue(Client[Ratio]; Client[No_]; VALUES(Client LE[Source No_])) )
2. second option would be to use an iteratior, something like:
SUMX( // can also be AVERAGEX, or other X function
ADDCOLUMNS(
SUMMERIZE(
Client;
Client[No_];
Client[Ratio];
"Days"; [Overdue Days];
"Debt"; [Overdue Debt]
);
"result";[Days]*([Debt]*Client[Ratio])
);
[result]
)
I have not tried this and this might include some small syntax error, but I hope this helps to achive what you are trying to do.Regards,
Kristjan76