cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Calculation for excluding some rows

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

Sample Data.JPG

 

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

 

 

View solution in original post

Highlighted
Microsoft
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 )

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: Calculation for excluding some rows

Hi @lukaspowerbi

 

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) 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Community Champion
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
 ) 

 

 

View solution in original post

Highlighted
Microsoft
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 )

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
Community Champion
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
Helper II

Re: Calculation for excluding some rows

Thank you both. 

@Ross73312   

@Phil_Seamark

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

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

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors