cancel
Showing results for
Did you mean:
Regular Visitor

## 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)

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super Contributor

## 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(
1,
0
),
1
) ```

Proud to be a Datanaut!

Super Contributor

## 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
Super Contributor

## 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!

Super Contributor

## 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(
1,
0
),
1
) ```

Proud to be a Datanaut!

Super Contributor

## 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!

Super Contributor

## 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)

Proud to be a Datanaut!

Regular Visitor

## Re: Calculation for excluding some rows

Thank you both.

@Phil_Seamark

Announcements

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 138 members 1,662 guests
Recent signins: