Forum Discussion
Unique values in calculated column
In direct query how can I achieve this calculated column using dax please
| Product Number | Colour ID | Calculated Column |
| 123 | 1 | 1 |
| 123 | 2 | 1 |
| 123 | 3 | 1 |
| 123 | 4 | 1 |
| 123 | 5 | 1 |
| 123 | 6 | 1 |
| 256 | A | A |
| 256 | C | A |
| 256 | d | A |
| 256 | e | A |
| 256 | f | A |
| 256 | g | A |
| 256 | h | A |
Hey! Sorry to hear that, maybe try this Dax measure:
Calculated_Column_Measure =
VAR FirstValue =
CALCULATE(
MIN('Table'[Colour ID]),
ALLEXCEPT('Table', 'Table'[Product Number])
)
RETURN
FirstValue----- CALCULATE(MIN('Table'[Colour ID])...) finds the minimum Colour ID per Product Number.
and ALLEXCEPT('Table', 'Table'[Product Number]) ensures that the calculation is performed only within each Product Number group.Tell me if this works! 😁😁
3 Replies
Hey there!
If I understand correctly, you want to create a calculated column in DirectQuery mode that assigns the same value to all rows with the same Product Number group.
I think you could use this DAX formula:
Calculated_Column =
VAR FirstValue =
MINX(
FILTER(
'Table',
'Table'[Product Number] = EARLIER('Table'[Product Number])
),
'Table'[Colour ID]
)
RETURN
FirstValueIt will identify the First Colour ID within each Product Number group. Use MINX to find the first unique value in Colour ID for each Product Number. And assign that value to all rows where Product Number is the same.
Hope this works!
😁😁
Function 'MINX' is not allowed as part of calculated column DAX expressions on DirectQuery models. I am getting this message
Hey! Sorry to hear that, maybe try this Dax measure:
Calculated_Column_Measure =
VAR FirstValue =
CALCULATE(
MIN('Table'[Colour ID]),
ALLEXCEPT('Table', 'Table'[Product Number])
)
RETURN
FirstValue----- CALCULATE(MIN('Table'[Colour ID])...) finds the minimum Colour ID per Product Number.
and ALLEXCEPT('Table', 'Table'[Product Number]) ensures that the calculation is performed only within each Product Number group.Tell me if this works! 😁😁
