Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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.
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.
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
125 | |
78 | |
76 | |
59 | |
51 |
User | Count |
---|---|
166 | |
84 | |
68 | |
67 | |
57 |