Forum Discussion

Icon for Helper III rank

Helper III

6 years ago

Solved

Adding a column to a calculatedtable groupby

I created calculatedtable "B" with groupby function from another table "A".

Table "A" has a date field.

I'd like to add the most recent dates from table "A" for each group in table "B".

Any ideas on how this coule be done?

yes, sorry

Column = CALCULATE(MAX(TableA[Date]), FILTER(ALL(TableA), TableA[group]=EARLIER(TableB[group])) )

4 Replies

az38
Community Champion
6 years ago
czuniga

try to create a column like

Column = CALCULATE(MAX(TableB[Date]), FILTER(ALL(TableB), TableB[group]=EARLIER(TableA[group])) )
- czuniga
  Helper III
  6 years ago
  az38
  Just to make sure I'm understanding, in this scenario:
  TableA = Original table
  TableB = CalculatedTable
  "group" = field TableB is groupedby
  Is that right?
  the date field doesn't exist in tableB, so if I change that to:
  
  Column = CALCULATE(MAX(TableA[Date]), FILTER(ALL(TableB), TableB[group]=EARLIER(TableA[group])) )
  
  I get the following error:
  
  "A single value for column 'group' in table 'TableA' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
  
  Thanks in advance for any other insights!
  - az38
    Community Champion
    6 years ago
    czuniga
    
    yes, sorry
    
    Column = CALCULATE(MAX(TableA[Date]), FILTER(ALL(TableA), TableA[group]=EARLIER(TableB[group])) )

Related content