How do I add more than one column to a filter statement in DAX?

[Anonymous]

I have a table with repeating values ​​in the index column, and a third column that has values ​​only in some rows

I wanted to create a new table in Power BI that selects the CompanyID column and the Start Date column (not Contact Date) and excludes the rows that have a blank value, so I used the following code:

calculateTable(Table, filter(Table, not(isblank(Table[Start Date]))))

The data now looks like this:

My question, however, is this: how do I add more columns without any blank values to the filter? i.e. Order Date, Number of Employees etc. I tried the below, but doesn't work

calculateTable(Table, filter(Table, not(isblank(Table[Start Date]), Table filter(Table not(isblank(Table[Order Date]), Table filter(Table not(isblank(Table[Employee Count]))))

[Anonymous]

Hi @Anonymous ,

According to my understand, you want to filter out the null in more than one column , right?

As @Greg_Deckler  said , just use FILTER() function to create a new table.(But it seems like the placement of the brackets is wrong...) The correct formula are as follows:

Table 2 =
FILTER (
    'Table',
    NOT ( ISBLANK ( 'Table'[Start Date] ) )
        && NOT ( ISBLANK ( 'Table'[Order Date] ) )
        && NOT ( ISBLANK ( 'Table'[Employee Count] ) )
)

Or you could use the following formula for measure and then apply it to Filter pane (set as “1”)

Measure =
IF (
    NOT ( ISBLANK ( MAX ( 'Table'[Start Date] ) ) )
        && NOT ( ISBLANK ( MAX ( 'Table'[Order Date] ) ) )
        && NOT ( ISBLANK ( MAX ( 'Table'[Employee Count] ) ) ),
    1,
    0
)

My visualizations look like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[Greg_Deckler]

@Anonymous First, no reason to use CALCULATETABLE, you just need FILTER, like this:

FILTER(Table, not(isblank(Table[Start Date]) && not(isblank(Table[Order Date])) && not(isblank(Table[Employee Count])))

or

FILTER(Table, not(isblank(Table[Start Date]) || not(isblank(Table[Order Date])) || not(isblank(Table[Employee Count])))

[Anonymous]

@Greg_Deckler The code works, but unfortunately, it selects all 350 columns of my original table, and not just the 3 columns I need: Start Date, Order Date, Employee Count. How can I select just these three columns, along with the index?

[Anonymous]

newTable =
SELECTCOLUMNS(
    FILTER(
        'Table',
        // You can do this since
        // BLANK() is treated as
        // FALSE() and any non-blank
        // value as TRUE(). Empty strings
        // "" and 0's are also treated as
        // FALSE().
        1
        && Table[Start Date]
        && Table[Order Date]
        && Table[Employee Count]
        // add more columns here
        // in the same vein if you
        // want to exclude rows with
        // BLANK in any of the columns.
    ),
    "Start Date", Table[Start Date],
    "Order Date", Table[Order Date],
    "Employee Count", Table[Employee Count]
)

[Anonymous]

@Greg_Deckler : The reason I was using CALCULATETABLE was to create a new table with columns added from the previous

Upon using the code, I get the error: The end of the input was reached. It also seems to randomly add additional parentheses for no reason to the end, and even if I remove them, they come back

Here's the code I used:

newTable = FILTER(Table, not(isblank(Table[Start Date]) && not(isblank(Table[Order Date])) && not(isblank(Table[Employee Count])))

Power BI auto corrected it to:

newTable = FILTER(Table, not(isblank(Table[Start Date]) && not(isblank(Table[Order Date])) && not(isblank(Table[Employee Count]))))))