Forum Discussion
CALCULATETABLE syntax?
Hello
Can CALCULATETABLE create a table from another table using:
1) columns matching (in) a specific list/array of column names
2) rows for each separately or all of the above filtered columns, matching specific criteria
So for example from the source table below:
| Col1 | Col2 | Col3 |
| A | 01/01/2000 | 1 |
| B | 02/01/2000 | 2 |
| C | 03/01/2000 | 3 |
To be able to CALCULATETABLE the below:
| Col1 | Col3 |
| A | 2 |
| B | 4 |
By choosing all columns that are in the list {Col1,Col3} and all rows that match Col2=CALCULATE(MAX(Col2),ALLEXCEPT(Col1)) (i.e. the rows where Col2 is the maximum date for each value of Col1.
I think the CALCULATETABLE, ADDCOLUMNS, SELECTCOLUMNS accept literal Column Names and not expressions and also you need to specify each one by one and not in a list?
Thanks!
Hi Anonymous ,
I think you may try this code to create a new table.
Table 2 = SELECTCOLUMNS ( FILTER ( 'Table', VAR _MAX_COL2 = CALCULATE ( MAX ( 'Table'[Col2] ), ALLEXCEPT ( 'Table', 'Table'[Col1] ) ) RETURN 'Table'[Col2] = _MAX_COL2 ), "Col1", 'Table'[Col1], "Col3", 'Table'[Col3] )My Sample:
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
I think you may try this code to create a new table.
Table 2 = SELECTCOLUMNS ( FILTER ( 'Table', VAR _MAX_COL2 = CALCULATE ( MAX ( 'Table'[Col2] ), ALLEXCEPT ( 'Table', 'Table'[Col1] ) ) RETURN 'Table'[Col2] = _MAX_COL2 ), "Col1", 'Table'[Col1], "Col3", 'Table'[Col3] )My Sample:
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Take a look at the GENERATE function. It allows you to create joins where there may not be lineage.
This isn't going to be syntactically accurate because I'm typing it in Notepad. But it should get you close...GENERATE( TableA, FILTER( TableB, Col1 = EARLIER(TableA[Col1) ) )Hope this helps!
