Forum Discussion
Solved
creating new columns conditionally
Hi, I'm struggling with references and conditions. Here is my table and I wan't te recreate Res column.
| ID | subID | Vol | Res |
| A | aa | 1000 | ac |
| A | ab | 250 | ac |
| A | ac | 5000 | ac |
| B | aa | 50 | ab |
| B | ab | 6000 | ab |
| B | ac | 300 | ab |
Res column is created by grouping by ID and selecting subID that has maximum Vol. Moving to Power BI from R where these reference seems logical, but here I can't get them to work at all.
Here is my try, but giving just blanks.
Res =
CALCULATE (
SELECTEDVALUE ( subID ),
FILTER ( ALLEXCEPT ( 'Table', ID ), Vol = MAX(Vol) )
)
hi viitama
Try the same pattern I gave you last time
Res = VAR MaxFig = CALCULATE ( MAX ( 'Table'[Vol] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) RETURN CALCULATE ( VALUES ( 'Table'[subID] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Vol] = MaxFig ) )
3 Replies
hi viitama
Try the same pattern I gave you last time
Res = VAR MaxFig = CALCULATE ( MAX ( 'Table'[Vol] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) RETURN CALCULATE ( VALUES ( 'Table'[subID] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Vol] = MaxFig ) )I had something like that, but it is giving me error "A table of multiple values was supplied where a single value was expected". Not sure why that fails. Actually change VALUES to SELECTEDVALUE and it works. Thanks.
