Unique values in calculated column

[Libin7963]

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

[freginier]

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
FirstValue

It 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!

😁😁

[Libin7963]

Function 'MINX' is not allowed as part of calculated column DAX expressions on DirectQuery models. I am getting this message

[freginier · Accepted solution]

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! 😁😁