cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brunoSE
Regular Visitor

select one values from multiple result of a comparison

I want to compare 2 value of my table 'STR'[ID STR Precedent] and 'STR'[ID STR] then each time [ID STR] is equal to [ID STR Precedent] I want to select the [ID STR Precedent] of this [ID STR].

 

I tried to use LOOKUPVALUE() but in many instance I have more than 1 [ID STR] which is equal to the original [ID STR Precedent]. And so it return an error because it can't take multiple result.

 

I tried to use CALCULATE() to dodge the issue but it just return blank when there is multiple match.

 

> ID Precedent AREA =
     var STRPreced = CALCULATE (
             SELECTEDVALUE ( 'STR'[ID STR Precedent]);
             FILTER (
                         ALLNOBLANKROW ( 'STR'[ID STR] );
                         'STR'[ID STR] == 'STR'[ID STR Precedent]
             );
             REMOVEFILTERS ( )
     )
     RETURN
STRPreced

 

 

Do you know a way to bypass this problem ? ( I tried FIRSTNONBLANK() but ran in the same error)

4 REPLIES 4
V-lianl-msft
Community Support
Community Support

Hi @brunoSE ,

 

You can create a measure to return the value of [ID STR precedence], when [ID STR] is equal to [ID STR precedence]

 

ID Precedent AREA2 = 
 var STRPreced = CALCULATE(
                    FIRSTNONBLANK('Table'[ID STR Precedent],1),
                    FILTER('Table','Table'[ID STR]='Table'[ID STR Precedent]))
return STRPreced

 

 You can also refer to this sample

 

Best Regards,

Liang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for your response but This is not result I am looking for.

 

If we take this table as example :

 

ID STRID STR Precedent
4567364676
7855545673
4563111367
1136715631
4567312397
1239778555
9463734975

 

I am looking for a match between the 2 columns. So it find the equality between the 2 field in red and return to me the value of the green field.

 

 

My script already does that however in the situation below:

 

ID STRID STR Precedent
4567364676
7855545673
4563111367
1136715631
4567312397
1239778555
9463734975

 

Where ID STR Precedent has multiple ID STR then I don't know how to make it return to me one of the orange field

Hi @brunoSE ,

 

When [ID STR] is equal to a value in [ID STR precedence], you can use the "IF" and "CONTAINS" functions to return the value of [ID STR precedence].

You can also refer to this sample .

 

Best Regards,

Liang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

HI @V-lianl-msft ,

 

Sorry I wasen't clear enough.

 

ID STRID STR PrecedentID STR AMONT
4567364676 
785554567364676 or 12397
456311136715631
1136715631 
456731239778555
123977855545673
9463734975 

 

 

What I want is the [ID STR Precedent] of the match. But I want it in the ligne of the first [ID STR Precedent]. 
The thing is how can I select one of the value when there is multiple match ?

 

Thanks for your time

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors