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.
Hello everyone, I have a question about Productx. I have a time period selected, and within the time period, I want to calculate the product of the "ReturnRate" column if there's a 1 in the "Action" column on the same row, ignoring the rows with 0 in "Action" column.
Especially, when I change my time slicer, the calculation should always start from 1 like it is showing as the "ExpectedResult" column.
My dax is showing like this:
MyResult =
CALCULATE (
PRODUCTX ( test, test[ReturnRate] * test[Action] + ( 1 - test[Action] ) ),
FILTER ( ALL ( test ), test[Date] <= MAX ( test[Date] ) )
)
But I want the result to look like the ExpectedResult.
Date | Action | ReturnRate | MyResult | ExpectedResult |
1/1/2021 | 1 | 1.010178 | 1.010178 | 1.000000 |
1/2/2021 | 1 | 1.002551 | 1.012755 | 1.010178 |
1/3/2021 | 0 | 0.980025 | 1.012755 | 1.012755 |
1/4/2021 | 0 | 0.957669 | 1.012755 | 1.012755 |
1/5/2021 | 0 | 0.947239 | 1.012755 | 1.012755 |
1/6/2021 | 1 | 0.979617 | 0.992112 | 1.012755 |
1/7/2021 | 0 | 0.970880 | 0.992112 | 0.992112 |
1/8/2021 | 1 | 0.986111 | 0.978333 | 0.992112 |
1/9/2021 | 1 | 0.966361 | 0.945423 | 0.978333 |
1/10/2021 | 0 | 0.925030 | 0.945423 | 0.945423 |
Is there any way to fix my dax and let it also work with my time slicer for different period? Thank you! Any help will be much appreciated!!
Solved! Go to Solution.
Havent looked closely, wife wants to watch a tv show with me.
But this is possibly part of your problem.
MyResult =
CALCULATE (
PRODUCTX ( test, test[ReturnRate] * test[Action] + ( 1 - test[Action] ) ),
FILTER ( ALL ( test ), test[Date] <= MAX ( test[Date] ) )
)
try
MyResult =
var MaxDate = max(test[Date])
return
CALCULATE (
PRODUCTX ( test, test[ReturnRate] * test[Action] + ( 1 - test[Action] ) ),
FILTER ( ALL ( test ), test[Date] <= MaxDate ) )
)
Haha you are definitely a great husband! But I don't think adding a variable fixed my problem tho ;-;
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 |
---|---|
52 | |
27 | |
24 | |
13 | |
9 |
User | Count |
---|---|
74 | |
58 | |
47 | |
16 | |
12 |