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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
relliott
Frequent Visitor

Show only values that occur after the first positive value

Hi,

 

I am struggling to find a solution to my problem and have searched the forum for hours trying to peice together a solution. 

 

I have a hypothetical table with 3 columns: Date, BusinessID, Sales. Using DAX, I want only show values from the Sales column that occur after each business' first initial positive Sale value. The first positive Sale acts as a trigger for us to start tracking further data, so I want to ignore all negative Sales values until a positive one occurs, then show all. In the below screenshot, the orange column has the desired behavior. 

 

I need to do this in DAX, because the Sales column is a measure that relies on a date from another table. 

 

Power BI Forum Question.PNG

 

 
2 ACCEPTED SOLUTIONS
HotChilli
Super User
Super User

This is a column since I don't have your data or measure.  No guarantees but you get the idea.

TxnsFromPos = VAR _col = CALCULATE(MIN(TableSale[Date]), TableSale[Sales] > 0 ,ALLEXCEPT(TableSale, TableSale[businessiD]))
RETURN  
   if (_col <= TableSale[Date], TableSale[Sales], BLANK())

 

Could I ask that in future you post your data (not a picture) and any relevant measure code (or pbix).  

Hope it helps.

View solution in original post

Greg_Deckler
Super User
Super User

I am thinking something like the following:

 

Column = 
  VAR __BusinessID = [BusinessID]
  VAR __Date = [Date]
  VAR __Table = FILTER('Table',[BusinessID] = __BusinessID && [Date] <= __Date)
  VAR __FirstPositive = MINX(FILTER(__Table,[Sales] > 0),[Date])
RETURN
  IF(__Date >= __FirstPositive,[Sales],BLANK())

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

I am thinking something like the following:

 

Column = 
  VAR __BusinessID = [BusinessID]
  VAR __Date = [Date]
  VAR __Table = FILTER('Table',[BusinessID] = __BusinessID && [Date] <= __Date)
  VAR __FirstPositive = MINX(FILTER(__Table,[Sales] > 0),[Date])
RETURN
  IF(__Date >= __FirstPositive,[Sales],BLANK())

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

This worked great! Thanks, Greg! I may have been overthinking it...

HotChilli
Super User
Super User

This is a column since I don't have your data or measure.  No guarantees but you get the idea.

TxnsFromPos = VAR _col = CALCULATE(MIN(TableSale[Date]), TableSale[Sales] > 0 ,ALLEXCEPT(TableSale, TableSale[businessiD]))
RETURN  
   if (_col <= TableSale[Date], TableSale[Sales], BLANK())

 

Could I ask that in future you post your data (not a picture) and any relevant measure code (or pbix).  

Hope it helps.

Thanks! I will share the pbix in the future. good advice!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.