Forum Discussion

Kiniaczek's avatar
Kiniaczek
Helper 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
camargos88's avatar
camargos88
Community Champion
6 years ago

Hi Kiniaczek ,

 

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

It should work.

Kiniaczek's avatar
Kiniaczek
Helper 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
    Community 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
      Helper 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
        Community Champion
        6 years ago

        Kiniaczek ,

         

        Can you explain it with examples and some data ?