Forum Discussion

Paramjit's avatar
Paramjit
Icon for Helper II rankHelper II
4 years ago
Solved

column with Maximum value with filter condition

Hi Team,   I have two columns ID and Actual Start Date in direct query.  I need another column to get the maximum of Actual Start date where ID = 1000   ID Actual Start date 1000 1-Jan-2...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Paramjit ，

    Since you are using Direct Query mode to connect to the data source, you cannot create a custom column. This is a limitation of Direct Query connection mode. You can review this official documentation below for more details.

    Limited data transformations

    There are also restrictions on creating calculated column as follows:

    Modeling limitations

    You can create a measure as suggested by amitchandak :

    Measure = MAXX(FILTER(ALLSELECTED('Table'),'Table'[ID]="1000"),[Actual Start date])

    Best Regards

Paramjit's avatar
Paramjit
Icon for Helper II rankHelper II
4 years ago

Hi amitchandak 

 

All the below three measures are not working. 

 

Actual start date is not find.

Maxx(filter(allselected(Table[ID]), Table[ID] =1000), Table[Actual Start date])

 

or It is returing only rows where ID=1000

calculate(max(Table[Actual Start date]), filter(allselected(Table[ID]), Table[ID] =1000 ))

 

or it is returing rows where ID=1000

calculate(max(Table[Actual Start date]), filter(all(Table[ID]), Table[ID] =1000 ))

 

Thank you,

Paramjit

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Paramjit , I tried these two seem to be working 

Measure = CALCULATE(max(ID1000[Actual Start date]), ID1000[ID]=1000)
Measure 2 = CALCULATE(max(ID1000[Actual Start date]), filter(all(ID1000[ID]), ID1000[ID]=1000))

 

file attached after signatures

  • Paramjit's avatar
    Paramjit
    Icon for Helper II rankHelper II
    4 years ago

    amitchandak  Thank you.

     

    Can we have something created using column rather than measure. Could you please confirm.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Paramjit ,

      You can create a calculated column as below to get it, please find the details in the attachment. Please note that you are now creating a calculated column that does not dynamically display values as the user interacts with it, as the value of a calculated column is computed during data refresh and uses the current row as a context.

      Column = MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[ID] = 1000 ), [Actual Start date] )

      Please find more details about the difference of calculated column vs measure from the following links.

      Calculated Columns and Measures in DAX

      Calculated Columns vs Measures

      Best Regards

      • Paramjit's avatar
        Paramjit
        Icon for Helper II rankHelper II
        4 years ago

        Anonymous thank you. 

         

        I am using direct query and not able to find the maxx and calculate function in custom column.

         

        thank you,

        Paramjit