Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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.
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())
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())
This worked great! Thanks, Greg! I may have been overthinking it...
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |