cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luiscb Member
Member

Filter Previous Data DAX on condition

Imagine I have the following dataset.

Product / IsOpen / Date
1           / 0          / 21 / 05 / 2009

1         / 1           / 22 / 05 / 2009

How can I filter/delete data previously to the first IsOpen = 1 ?? By creating a new table or even in Powerquery. I just want data starting from its first IsOpen = 1 per Product.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
fhill Established Member
Established Member

Re: Filter Previous Data DAX on condition

I've done this with 2 calcualted columns...  (You could combine them in 1 if you wanted).

 

VisCheck looks for First Date IsOpen = 1 comparing each Produt Line to the EARLIER Product line.  This allows for different dates depending on Product.  Then Yes-No compares the dates placing 0 or 1.  I then Filter by only 1.


** I noticed you said "Product A Completely Gone", but there was 1 IsOpen with a date?  Mabye you can play with the > or >= sign in the VisCheck to acomplish what you need.

 

VisCheck = CALCULATE(FIRSTDATE(Prod[Date]), Prod[IsOpen] = "1" ,FILTER( Prod, Prod[Product] = EARLIER(Prod[Product])))

Yes-NO = IF(Prod[Date] < Prod[VisCheck],0,1)

 

Thank You,

FOrrest

 

Capture.PNG

View solution in original post

4 REPLIES 4
fhill Established Member
Established Member

Re: Filter Previous Data DAX on condition

... is that first IsOpen for ALL products, or by Product?  What about Products that close after opening?  What is your desired output based on these values?

 

ProductIsOpenDate
A01/2/2012 0:00
A01/5/2012 0:00
A11/7/2012 0:00
B13/1/2012 0:00
B13/5/2012 0:00
B13/7/2012 0:00
C06/2/2012 0:00
C16/4/2012 0:00
C16/6/2012 0:00
D012/20/2011 0:00
D112/23/2011 0:00
D012/31/2012 0:00
luiscb Member
Member

Re: Filter Previous Data DAX on condition

by product.
So in your dataset, this would happen:

Product A completely gone
Product B every row
PRoduct C the last two.. since the first one is IsOpen = 0 D the last two as well..

Products that close after opening would appear.. as long as opening is first, then everything appears.. everything that appears as closed before the first isOpen = 1 should be gone.
I think I might found the solution, will let you know if it works

Highlighted
fhill Established Member
Established Member

Re: Filter Previous Data DAX on condition

I've done this with 2 calcualted columns...  (You could combine them in 1 if you wanted).

 

VisCheck looks for First Date IsOpen = 1 comparing each Produt Line to the EARLIER Product line.  This allows for different dates depending on Product.  Then Yes-No compares the dates placing 0 or 1.  I then Filter by only 1.


** I noticed you said "Product A Completely Gone", but there was 1 IsOpen with a date?  Mabye you can play with the > or >= sign in the VisCheck to acomplish what you need.

 

VisCheck = CALCULATE(FIRSTDATE(Prod[Date]), Prod[IsOpen] = "1" ,FILTER( Prod, Prod[Product] = EARLIER(Prod[Product])))

Yes-NO = IF(Prod[Date] < Prod[VisCheck],0,1)

 

Thank You,

FOrrest

 

Capture.PNG

View solution in original post

luiscb Member
Member

Re: Filter Previous Data DAX on condition

This is a good solution, in my case the time is also important.
Anyway, decided to do via power query and it works. 
Thanks!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 46 members 1,075 guests
Please welcome our newest community members: