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

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

Paramjit , Try measure like

 

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

 

or

 

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

 

or

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

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