cancel
Showing results for
Did you mean:
Member

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

Accepted Solutions
Member

Re: Lookupvalue in measure

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
Member

Re: Lookupvalue in measure

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

Highlighted
Member

Re: Lookupvalue in measure

Thanks, @Kristjan76

The second solution, solved the problem!

Announcements

Power BI Super User, Greg Deckler, explains

Power Platform Summit North America

Register by September 5 to save \$200

Back to School Contest

Engage and empower students with Power BI!

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 34 members 1,021 guests
Recent signins: