Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous ,

 

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.

Anonymous
Not applicable

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 @Anonymous ,

 

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.

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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