Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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 )
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)
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 )
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |