Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Use CALCULATE() without using expression

Hello people  🙂
First of all, here's my PBI file: test2 

I have a small problem in a calculated table that I created.
My standard table follows this structure:

CODDATELEVEL
0012/01/2020M
0012/03/20201H
1112/03/20201H
1112/05/2020A
1112/05/2020A

 

As you can see, in the COD column the records can be repeated and the DATE column can have different dates for the same COD.
I would like to create a calculated table that shows all distinct and most recent CODs, and their respective LEVEL.
Based on the example table above, the new table would be somethign like this:

CODDATELEVEL
0012/03/20201H
1112/05/2020A

 

With this calculated table, I can get all  distinct and most recent CODs:

_newTable = SUMMARIZE('table'; 'table'[COD]; "LEVEL"; CALCULATE(MAX('table'[LEVEL]); ALLEXCEPT('table';'table'[COD])); "MAX VALUE"; CALCULATE(MAX('table'[DATE]);ALLEXCEPT('table';'table'[COD])))

The only problem in this calculated table is that the LEVEL column does not show the actual LEVEL for that COD and that DATE. It shows the highest order character on the alphabetical scale and associates it with the most recent COD. I found that the problem lies in that part of the code:

"LEVEL", CALCULATE(MAX('table'[LEVEL])

This brings the higher letter of that specific COD on the alphabetical scale (if the same COD has LEVEL A, B and Z, it will always show the letter Z). An example of this is analyzing the COD 40501455 (in the .pbix file). The most recent LEVEL of this COD is 1D, but the _newTable shows H because H is above a LEVEL that starts with a number on the alphabetic scale.
But if I just remove this MAX from the code, it points out a syntax error.
Can someone please help me?



  • Anonymous ,

    Try like

    filter(SUMMARIZE('table'; 'table'[COD]; 'table'[LEVEL]; 'table'[DATE],"max_dt"CALCULATE(MAX('table'[DATE]);ALLEXCEPT('table';'table'[COD])))
    ,[DATE]=[max_dt])

3 Replies

  • Anonymous ,

    Try like

    filter(SUMMARIZE('table'; 'table'[COD]; 'table'[LEVEL]; 'table'[DATE],"max_dt"CALCULATE(MAX('table'[DATE]);ALLEXCEPT('table';'table'[COD])))
    ,[DATE]=[max_dt])
    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you so much amitchandak !!!!!
      This is exactly what I want!

  • Vvelarde's avatar
    Vvelarde
    Community Champion

    Anonymous 

     

    Hi, try creating this DAX Table:

    ResultTable =
    SUMMARIZECOLUMNS (
        Table1[COD];
        "MAX"; MAX ( 'Table1'[Date] );
        "LEVEL"; CALCULATE (
            VALUES ( Table1[LEVEL] );
            FILTER ( Table1; Table1[DATE] = MAX ( Table1[DATE] ) )
        )
    )

     

    Regards

     

    Victor