Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bebs
Helper II
Helper II

How 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 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

 

 

2 ACCEPTED SOLUTIONS

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 )

View solution in original post

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.

 

View solution in original post

9 REPLIES 9
Bebs
Helper II
Helper II

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"

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

 

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

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.

 

Yeah, thanks a lot

Bebs
Helper II
Helper II

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

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 )

Thanks, it works 🙂

PabloDeheza
Solution Sage
Solution Sage

Hi there!

Try something like the following:

CALCULATETABLE(

VALUES( 'Table'[A] ),

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

)

Let me know if that helps!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors