cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rimgri Member
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
Kristjan76 Member
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
  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 2
Kristjan76 Member
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
  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
    

Highlighted
rimgri Member
Member

Re: Lookupvalue in measure

Thanks, @Kristjan76

 

The second solution, solved the problem!

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 34 members 1,021 guests
Please welcome our newest community members: