How to create table using DAX (some questions on SUMMARIZE Function)

[Bebs]

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 using SUMMARIZE function :

NewTable = SUMMARIZE(Table,'Table'[A])  <= I've got all distinct values of A (which is a good start)

1) Now, how can I filter to keep only values from A field where 'Table'[B]=1 ? (What is the best efficient way ?)

2) Then I'll have to concatenate (union) with all values of A where C=2, what is the easiest / efficient way ? Am I forced to build to different tables then concatenate / append / union it or can I create it in one Dax command ?

Best regards

[PabloDeheza · Accepted solution]

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]

One last question @PabloDeheza : is it possible to add a condition column, for example :

TABLE :

A, B, C

5,1,0

6,2,1

7,3,2

8,1,2

Wanted result :

NEWTable:

A,BorC

5, 1, "FilterOne"

8, 1, "FilterOne"

7, 2 , "FilterTwo"

8, 2, "FilterTwo"

[PabloDeheza]

Sure, after all that you can just add a new column to the table with the following DAX:

Filter = 
IF(
	'New Table'[B] = 1,
	"Filter One",
	"Filter Two"
)

[Bebs]

Hello @PabloDeheza :

I've one last question :

I'm fighting with syntax 🙂

how can I Mix :

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 )

and

Filter = 
IF(
	'New Table'[B] = 1,
	"Filter One",
	"Filter Two"
)

  ?

(That is say integrate the new manuel string column in the big table) ?

Regards

[PabloDeheza · Accepted solution]

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.

[Bebs]

Yeah, thanks a lot

[Bebs]

Thanks,

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 · Accepted solution]

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 🙂

[PabloDeheza]

Hi there!

Try something like the following:

CALCULATETABLE(

VALUES( 'Table'[A] ),

'Table'[B] = 1,
'Table'[C] = 2

)

Let me know if that helps!