Forum Discussion

Kiniaczek's avatar
Kiniaczek
Icon for Helper I rankHelper I
6 years ago

How to find Max in group without using ALLEXCEPT

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...
Need Help
Kiniaczek's avatar
Kiniaczek
Icon for Helper I rankHelper I
6 years ago

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's avatar
camargos88
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    Kiniaczek
    Icon for Helper I rankHelper I
    6 years ago

    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's avatar
      camargos88
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Kiniaczek ,

       

      Can you explain it with examples and some data ?