Forum Discussion

Bebs's avatar
Bebs
Helper II
3 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 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

 

 

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

     

9 Replies

  • PabloDeheza's avatar
    PabloDeheza
    Solution Sage
    3 years ago

    Hi there!

    Try something like the following:

    CALCULATETABLE(

    VALUES( 'Table'[A] ),

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

    )

    Let me know if that helps!

     

  • Bebs's avatar
    Bebs
    Helper II
    3 years ago

    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's avatar
      PabloDeheza
      Solution Sage
      3 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 )
  • Bebs's avatar
    Bebs
    Helper II
    3 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
      Solution Sage
      3 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
        Helper II
        3 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