Forum Discussion

Libin7963's avatar
Libin7963
Helper II
1 year ago
Solved

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 ...
Need Help
  • freginier's avatar
    freginier
    1 year ago

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

Libin7963's avatar
Libin7963
Helper II
1 year ago

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

freginier's avatar
freginier
Solution Sage
1 year ago

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