DAX Count Max Value By Group

1
votes

I have a survey that is sent to companies periodically and each new survey is given a collection #. I'm trying to create a column that tells me the maximum collection # for each company code. This will vary by company because not every company responds to every survey.

LastCollection = (FILTER('Dynamic', 'Dynamic'[Collection]
=CALCULATE(max('Dynamic'[Collection]),ALLEXCEPT('Dynamic','Dynamic'[Company Name])))

The error I'm getting is that the expression refers to multiple columns. I attempted to wrap it in AVERAGE but that didn't help.

2
powerbidax

2 Answers

1
votes

You should just be able to do something along the lines of the below.

LastCollection = 
     VAR companyName = 'Dynamic'[Company Name]
     RETURN CALCULATE( MAX ('Dynamic'[Collection] ),
                  FILTER ( 'Dynamic', 'Dynamic'[Company Name] = companyName ))
1
votes

I'm not sure why you have the CALCULATE inside a FILTER since a FILTER returns a table rather than a single value.

This should be simpler:

LastCollection =
CALCULATE (
    MAX ( 'Dynamic'[Collection] ),
    ALLEXCEPT ( 'Dynamic', 'Dynamic'[Company Name] )
)