Forum Discussion
Selecting one value from column of another table with multiple values (Based on hierarchy criteria
I am trying to pull one value into a new table based on a column in another table with multiple values. The key is, I want to pull one distinct value based on a criteria. The values in the 2nd table are as:
| Name | Value |
| A | 1 |
| A | 1 |
| A | 2 |
| B | 1 |
| B | 1 |
| B | 3 |
| C | 1 |
| C | 1 |
| C | 4 |
| D | 1 |
| D | 1 |
| D | 5 |
| E | 1 |
| E | 1 |
| E | 4 |
Desired result is below as:
Pull the distinct value but:
If the value 2 is present, then pull 2
If the value 5 is present, then pull 5
If the value 1 is present, then pull 1
| Name | Value |
| A | 2 |
| B | 1 |
| C | 1 |
| D | 5 |
| E | 1 |
Hi Anonymous
please try
Values = VAR CurrentName = Table1[Nsme] VAR T1 = FILTER ( Table2, Table2[Nsme] = CurrentName ) VAR T2 = DISTINCT ( SELECTCOLUMNS ( T1, "@Value", [Value] ) ) RETURN SWITCH ( TRUE (), 2 IN T2, 2, 5 IN T2, 5, 1 IN T2, 1 )
2 Replies
- tamerj1
Community Champion
Hi Anonymous
please try
Values = VAR CurrentName = Table1[Nsme] VAR T1 = FILTER ( Table2, Table2[Nsme] = CurrentName ) VAR T2 = DISTINCT ( SELECTCOLUMNS ( T1, "@Value", [Value] ) ) RETURN SWITCH ( TRUE (), 2 IN T2, 2, 5 IN T2, 5, 1 IN T2, 1 )