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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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