cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Calculation for excluding some rows

Below is a sample data that I brought into power bi.

I am trying to exclude rows with Gift Date < 7/1/2017 and Payment received date is NULL(empty fields),

but want to keep the rows where Gift Date < 7/1/2017 and Payment received date is NOT NULL

(In my sample data, there are three examples/three records that I want to keep, the three rows with Payment Recieved date 7/28/2017 and two 7/7/2017 dates)

I would think there must be a calculated column added? Can someone please help me with the solution?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion

## Re: Calculation for excluding some rows

I think @Phil_Seamark is close, but it doesn't appear to met the criteria.  I propose this altered version.

```Calculated Column = IF(
'Table2'[Payment Recieved Date] = BLANK(), IF(
'Table2'[Gift Date] >= DATE(2017,7,1),
1,
0
),
1
) ```

Highlighted
Microsoft

## Re: Calculation for excluding some rows

@Ross73312 Oh yeah, I misread and your's looks better.

I hate nested IF statements so reckon this could be converted to the more readable

```col = SWITCH (              TRUE() ,             -- WHEN --
A=A && B=B , 1 ,
C=D , 1             -- ELSE --
0
) ```

Proud to be a Datanaut!

5 REPLIES 5
Highlighted
Microsoft

## Re: Calculation for excluding some rows

Something like this might be close to what you need

```Calculated Column = IF(
'Table2'[Gift Date] > DATE(2017,7,1) && 'Table2'[Payment Recieved Date] <> BLANK(),
1,
0) ```

Proud to be a Datanaut!

Highlighted
Community Champion

## Re: Calculation for excluding some rows

I think @Phil_Seamark is close, but it doesn't appear to met the criteria.  I propose this altered version.

```Calculated Column = IF(
'Table2'[Payment Recieved Date] = BLANK(), IF(
'Table2'[Gift Date] >= DATE(2017,7,1),
1,
0
),
1
) ```

Highlighted
Microsoft

## Re: Calculation for excluding some rows

@Ross73312 Oh yeah, I misread and your's looks better.

I hate nested IF statements so reckon this could be converted to the more readable

```col = SWITCH (              TRUE() ,             -- WHEN --
A=A && B=B , 1 ,
C=D , 1             -- ELSE --
0
) ```

Proud to be a Datanaut!

Highlighted
Community Champion

## Re: Calculation for excluding some rows

I completely share your hatred for nested IFs.  I often find they are easier to read/understand up to about 3 deep.  Thus for questions from people of unknown skill level, i tend to recommend solutions are geared towards the learning experience rather than optimisation. (too a point of course)

Highlighted
Helper II

## Re: Calculation for excluding some rows

Thank you both.

@Phil_Seamark

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors