How to find Max in group without using ALLEXCEPT

[Kiniaczek]

Hello,

I want to find latest date for different areas.

I have table:

Date; AreaId; Other Values

When I use

newcolumn = CALCULATE(MAX(myTable[Date]);ALLEXCEPT(myTable;myTable[AreaId]))

 visual slicers don't work. I want to slice by Date on the visual. What should I use?

Regards

[camargos88]

Hi @Kiniaczek ,

Are you using this code to create a column ? Try this same code with measure.

It should work.

[Kiniaczek]

As I understand ALLEXCEPT and CALCULATE removes any filters from outside the query. How it can be different with measure?

Anyway I don't want to use measure because using measures changes my table visualization from LEFTJOIN to carthesian product of columns. I was asking about it in different topic but no one replied and I focused on using columns.

[camargos88]

@Kiniaczek ,

ALL function removes any applied filter to the table.

ALLEXCEPT keep the filters named in the function.

Also, if you want it dynamic, you need a measure. Applying it to a column will make any filter result the same returned value.

Columns are generated after a refresh of your data.

[Kiniaczek]

After this step I wanted to use IF, compare Date with MaxDateColumn value, get one of other columns value if true and BLANK() when false.  After that use average on table visualization. But with measure I'm not able to compare measure and column in IF statement...

Now I completely don't know what to do 😞

I thought it could be possible to use ALLSELECTED to not remove filters and than group by AreaId.

[camargos88]

@Kiniaczek ,

Can you explain it with examples and some data ?