Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 ;-;
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
13 |
User | Count |
---|---|
91 | |
86 | |
46 | |
28 | |
21 |