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.
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!
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |