Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
Proud to give back to the community!
Thank You!
... 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?
Product | IsOpen | Date |
A | 0 | 1/2/2012 0:00 |
A | 0 | 1/5/2012 0:00 |
A | 1 | 1/7/2012 0:00 |
B | 1 | 3/1/2012 0:00 |
B | 1 | 3/5/2012 0:00 |
B | 1 | 3/7/2012 0:00 |
C | 0 | 6/2/2012 0:00 |
C | 1 | 6/4/2012 0:00 |
C | 1 | 6/6/2012 0:00 |
D | 0 | 12/20/2011 0:00 |
D | 1 | 12/23/2011 0:00 |
D | 0 | 12/31/2012 0:00 |
Proud to give back to the community!
Thank You!
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
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
Proud to give back to the community!
Thank You!
This is a good solution, in my case the time is also important.
Anyway, decided to do via power query and it works.
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |