topic Show only values that occur after the first positive value in DAX Commands and Tips

Show only values that occur after the first positive value

relliott — Thu, 05 Mar 2020 00:23:45 GMT

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.

Re: Show only values that occur after the first positive value

HotChilli — Thu, 05 Mar 2020 01:24:00 GMT

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.

Re: Show only values that occur after the first positive value

Greg_Deckler — Thu, 05 Mar 2020 04:12:53 GMT

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())

Re: Show only values that occur after the first positive value

relliott — Thu, 05 Mar 2020 05:16:59 GMT

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

Re: Show only values that occur after the first positive value

relliott — Thu, 05 Mar 2020 05:18:23 GMT

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