Using Filter with UNION in DAX

[abbababa1]

Hi,

I am trying to flatten a table and filter by AAA values <> 0. So far the union works, but the filters not. How to approach this?

Renewals_flat =

    UNION (

        SELECTCOLUMNS (

        'Renewals',

        "Date", Renewals[CALENDAR_DATE],

        "GEO", Renewals[GEO],

        "Type", "New_Logo",

        "AAA", Renewals[NEW_LOGO]

    ),

        SELECTCOLUMNS (

        'Renewals',

        "Date", Renewals[CALENDAR_DATE],

        "GEO", Renewals[GEO],

        "Type", "New_Expansion",

        "AAA", Renewals[NEW_EXPANSION]

    ),

        SELECTCOLUMNS (

        'Renewals',

        "Date", Renewals[CALENDAR_DATE],

        "GEO", Renewals[GEO],

        "Type", "Gross New",

        "AAA", Renewals[_GROSS_NEW_ALL]

    )

)

[FreemanZ · Accepted solution]

hi @abbababa1 

how about filter the 'Renewals' table first like:

VAR _table=

CALCULATETABLE(

    Renewals, 

    Renewals[NEW_LOGO]<>BLANK(),

    Renewals[NEW_EXPANSION]<>BLANK(),

    Renewals[_GROSS_NEW_ALL]<>BLANK(),

)

then use _table instead of Renewals in the rest of your code.

[mariussve1]

Hi,

This should work:

Union (

     Selectcolumns (

       Filter (

             Table,
             Table[Column] <> 0

        ),

        Col1, Col2......

),

Selectcolumns ( Filter (

..........

Br

Marius