cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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?

1 ACCEPTED SOLUTION
Responsive Resident

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
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 2
Responsive Resident

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

Anonymous
Not applicable

Thanks, @Kristjan76

The second solution, solved the problem!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.