Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
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.
Thank you for your response but This is not result I am looking for.
If we take this table as example :
ID STR | ID STR Precedent |
45673 | 64676 |
78555 | 45673 |
45631 | 11367 |
11367 | 15631 |
45673 | 12397 |
12397 | 78555 |
94637 | 34975 |
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 STR | ID STR Precedent |
45673 | 64676 |
78555 | 45673 |
45631 | 11367 |
11367 | 15631 |
45673 | 12397 |
12397 | 78555 |
94637 | 34975 |
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.
HI @V-lianl-msft ,
Sorry I wasen't clear enough.
ID STR | ID STR Precedent | ID STR AMONT |
45673 | 64676 | |
78555 | 45673 | 64676 or 12397 |
45631 | 11367 | 15631 |
11367 | 15631 | |
45673 | 12397 | 78555 |
12397 | 78555 | 45673 |
94637 | 34975 |
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
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |