Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lukaspowerbi
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
Anonymous
Not applicable

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

@Anonymous 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
Phil_Seamark
Employee
Employee

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!

Anonymous
Not applicable

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
 ) 

 

 

@Anonymous 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!

Anonymous
Not applicable

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)

Thank you both. 

@Anonymous   

@Phil_Seamark

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.