Forum Discussion
Bebs
Helper II
Helper IIHow to create table using DAX (some questions on SUMMARIZE Function)
Hello, I've a table (let's call it 'Table' 😁) with fields A, B, C I'd like to create a new table with all values from A where B=1 and all values from A where C=2. I'm trying to create a table ...
Alright, this should do it 😀
New Table = VAR _Table1 = FILTER( SUMMARIZE( 'Table', 'Table'[A], 'Table'[B] ), 'Table'[B] = 1 ) VAR _Table2 = FILTER( SUMMARIZE( 'Table', 'Table'[A], 'Table'[C] ), 'Table'[C] = 2 ) RETURN UNION( _table1, _Table2 )To do all in one DAX the code would be this one:
New Table = VAR _Table1 = FILTER( SUMMARIZE( 'Table', 'Table'[A], 'Table'[B] ), 'Table'[B] = 1 ) VAR _Table2 = FILTER( SUMMARIZE( 'Table', 'Table'[A], 'Table'[C] ), 'Table'[C] = 2 ) RETURN ADDCOLUMNS ( UNION ( _table1, _Table2 ), "Filter", IF ( 'Table'[B] = 1, "Filter One", "Filter Two" ) )Note that in the last line of code, the word "Filter" is the name of your new colunm, so it can be called as desired.
BebsHelper II
Helper IIThanks,
to precise one tricky point : i'd like to append all lines of both scenarios,
to illustrate this point
suppose the dataset is this :
TABLE :
A, B, C
5,1,0
6,2,1
7,3,2
8,1,2
Wanted result :
NEWTable:
A,BorC
5,1
8,1
7,2
8,2
- PabloDeheza
Solution Sage
Alright, this should do it 😀
New Table = VAR _Table1 = FILTER( SUMMARIZE( 'Table', 'Table'[A], 'Table'[B] ), 'Table'[B] = 1 ) VAR _Table2 = FILTER( SUMMARIZE( 'Table', 'Table'[A], 'Table'[C] ), 'Table'[C] = 2 ) RETURN UNION( _table1, _Table2 )- Bebs
Thanks, it works 🙂