Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
COD | DATE | LEVEL |
00 | 12/01/2020 | M |
00 | 12/03/2020 | 1H |
11 | 12/03/2020 | 1H |
11 | 12/05/2020 | A |
11 | 12/05/2020 | A |
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:
COD | DATE | LEVEL |
00 | 12/03/2020 | 1H |
11 | 12/05/2020 | A |
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?
Solved! Go to Solution.
@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 ,
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |