topic Re: How to create table using DAX (some questions on SUMMARIZE Function) in DAX Commands and Tips

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

Bebs — Tue, 06 Sep 2022 09:45:07 GMT

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

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

PabloDeheza — Tue, 06 Sep 2022 12:32:28 GMT

Hi there!

Try something like the following:

CALCULATETABLE(

VALUES( 'Table'[A] ),

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

)

Let me know if that helps!

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

Bebs — Tue, 06 Sep 2022 15:42:03 GMT

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

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

PabloDeheza — Tue, 06 Sep 2022 16:57:12 GMT

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 )

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

Bebs — Wed, 07 Sep 2022 12:40:15 GMT

Thanks, it works 🙂

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

Bebs — Wed, 07 Sep 2022 13:39:15 GMT

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"

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

PabloDeheza — Wed, 07 Sep 2022 15:40:27 GMT

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" )

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

Bebs — Mon, 12 Sep 2022 10:01:45 GMT

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

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

PabloDeheza — Mon, 12 Sep 2022 12:20:22 GMT

To do all in one DAX the code would be this one:

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.

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

Bebs — Tue, 13 Sep 2022 09:19:10 GMT

Yeah, thanks a lot