Forum Discussion

Bebs's avatar
Bebs
Icon for Helper II rankHelper II
4 years ago
Solved

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 ...
DAX
needs help
summarize table
  • PabloDeheza's avatar
    PabloDeheza
    4 years ago

    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 )
  • PabloDeheza's avatar
    PabloDeheza
    4 years ago

    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's avatar
Bebs
Icon for Helper II rankHelper II
4 years ago

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's avatar
    PabloDeheza
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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's avatar
      Bebs
      Icon for Helper II rankHelper II
      4 years ago

      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's avatar
        PabloDeheza
        Icon for Solution Sage rankSolution Sage
        4 years ago

        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.