CALCULATETABLE syntax?

[UsePowerBI]

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! 

[v-rzhou-msft · Accepted solution]

Hi @UsePowerBI ,

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[littlemojopuppy]

Hi @UsePowerBI 

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!