is there a way to filter a table by a column added to it in a single expression

[zwaters3]

Hey guys, is there a way to perform this sort of operation?:

New Table = 
VAR crossjoinedAB = CROSSJOIN('tableA', 'tableB'
VAR crossjoinedABwithNewCol = AddColumn(crossjoinedAB, "FilterByThisColumn", IF( [ColumnInA] = 1, 1, 0))
RETURN CALCULATETABLE(crossjoinedABwithNewCol, [FilterByThisColumn] = 1)

As of right now I cannot get away with adding a new column, and then filtering by it in the same expression. I can get around this by splitting this into two parts, where part 1 makes the table with the new column

New Table = 
VAR crossjoinedAB = CROSSJOIN('tableA', 'tableB'
VAR crossjoinedABwithNewCol = AddColumn(crossjoinedAB, "FilterByThisColumn", IF( [ColumnInA] = 1, 1, 0))

and part 2, is when I make ANOTHER new table to filter it.

New Table2 = 
RETURN CALCULATETABLE('New Table', [FilterByThisColumn] = 1)

Is there a way to do this in a single step, without having to create an intermediary table?

[mwegener]

Hi @zwaters3 ,

why don't you use power query for transformation?

[zwaters3]

I already have this transformation up and running through power query, I'm just trying to recreate it for testing which loads faster. 

[mwegener · Accepted solution]

Hi @zwaters3 ,

try this

New Table = 
VAR crossjoinedAB = CROSSJOIN('tableA', 'tableB')
VAR crossjoinedABwithNewCol = ADDCOLUMNS(crossjoinedAB, "FilterByThisColumn", IF( [ColumnInA] = 1, 1, 0))
RETURN FILTER(crossjoinedABwithNewCol, [FilterByThisColumn] = 1)

[mwegener]

Hi @zwaters3 ,

from experience I would say Power Query.
Power Query has the advantage that with query folding, the data may already be filtered in the source and that the result is saved with a higher compression.

[zwaters3]

I understand that may be true, but I would still prefer to have an answer to my original question. 

[zwaters3]

sorry, you provided the answer just as I responded to your previous post