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.