I need to create a calculated column in order to filter a Tabular model table with the following structure:
Table1
| ID | Attr A | Attr B | Value |
|-----|-----------|--------|-------|
| 123 | text here | blah | 130 |
| 123 | blah | blah | 70 |
| 456 | blah | blah | 90 |
| 456 | blah | blah | 110 |
And I want the following new column to be created:
| ID | Attr A | Attr B | Value | MaxValue |
|-----|-----------|--------|-------|----------|
| 123 | text here | blah | 130 | TRUE |
| 123 | blah | blah | 70 | FALSE |
| 456 | blah | blah | 90 | FALSE |
| 456 | blah | blah | 110 | TRUE |
I would like to create a calculated column using Power Query equivalent to the following DAX statement which returns TRUE if the Values column is the largest for a given ID, FALSE otherwise.
= IF(CALCULATE(MAX('Table1'[Value]),ALLEXCEPT('Table1','Table1'[ID])) = 'Table1'[Value], TRUE(), FALSE())
P.S. I used the default M language editor to generate an if shell statement so this is similar to what I'm looking for:
= Table.AddColumn(#"Changed Type", "MaxValue", each if [#"[Value]"] = 'some logic here' then true else false)