Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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
Kristjan76
Responsive Resident
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
  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
    

View solution in original post

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

Anonymous
Not applicable

Thanks, @Kristjan76

 

The second solution, solved the problem!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.