Forum Discussion

Kish1999's avatar
Kish1999
Icon for Helper II rankHelper II
2 years ago
Solved

Get Latest comment for a group

Hello All,

I have a requirement where i need to get the latest comment for a department and ID. So if ID '1' and Department 'X' is slected in the filters, I should get the latest comment that was added for that department and ID. I have tried addding a calculated column as .. and then getting the comment corresponding to that date but this does not work

MaxModDate =
CALCULATE (
    MAX ( 'QPlans Historical (Prod)'[Modified] ),
    ALLEXCEPT (
        'QPlans Historical (Prod)',
        'QPlans Historical (Prod)'[Department],
        'QPlans Historical (Prod)'[Program ID]
    )
)

Please see below the sample dataset, Can you please help me achieve this.

DepartmentIDModified DateComments
    
X101-01-2024 
X110-01-2024abcdef
X111-02-2024qwert
Y105-01-2024 
Y112-01-2024XCV
X202-01-2024SDF
X211-01-2024CVB
    
    
Expected Result when Month "January" and ID "1" is selected
    
DepartmentIDComments 
    
X1abcdef 
Y1XCV 
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Kish1999 Use ALLSELECTED instead of ALL. 

    Measure = 
  VAR __Dept = MAX('QPlans Historical'[Department])
  VAR __ID = MAX('QPlans Historical'[ID])
  VAR __Date = MAX('QPlans Historical'[Modified Date])
  VAR __MaxDate = MAXX(FILTER(ALLSELECTED('QPlans Historical'), [Department] = __Dept && [ID] = __ID), [Modified Date])
  VAR __Result = IF( __Date = __MaxDate, 1, 0)
RETURN
  __Result

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Kish1999 This would be a Complex Selector like so:

    The Complex Selector - Microsoft Fabric Community

    Measure = 
  VAR __Dept = MAX('QPlas Historical'[Department])
  VAR __ID = MAX('QPlans Historical'[ID])
  VAR __Date = MAX('QPlans Historical'[Modified Date])
  VAR __MaxDate = MAXX(FILTER(ALL('QPlans Historical'), [Department] = __Dept && [ID] = __ID), [Modified Date])
  VAR __Result = IF( __Date = __MaxDate, 1, 0)
RETURN
  __Result
    • Kish1999's avatar
      Kish1999
      Icon for Helper II rankHelper II
      2 years ago

      Hello Greg_Deckler , Thank You for the solution. This measure does not give me the Max date for each of the slected month in the slicer. It returns the overall MaxDate for the Department and ID, not based on the month slected in the slicer.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Kish1999 Use ALLSELECTED instead of ALL. 

        Measure = 
  VAR __Dept = MAX('QPlans Historical'[Department])
  VAR __ID = MAX('QPlans Historical'[ID])
  VAR __Date = MAX('QPlans Historical'[Modified Date])
  VAR __MaxDate = MAXX(FILTER(ALLSELECTED('QPlans Historical'), [Department] = __Dept && [ID] = __ID), [Modified Date])
  VAR __Result = IF( __Date = __MaxDate, 1, 0)
RETURN
  __Result