I need to create a dynamic calculated measure, using an if statement to use one of two what if parameters, depending on the value in a row-level dimension. The measure will need to be summed at the end.
Example:
F[AMOUNT]*IF(F[THING]='Y','WHAT IF 1'[WHAT IF 1 VALUE],'WHAT IF 2'[WHAT IF 2 VALUE])
Where 'WHAT IF 1' and 'WHAT IF 2' are what if parameters. This formula returns an error saying "Cannot find name '[THING]'. I believe this is because [THING] isn't a measure, but I need it to be evaluated at the row level so I can't simply add an aggregation. How can I accomplish this?
Hi @Dbro020
If you are using measure try to add
=MAXX( F), F[AMOUNT]*IF(F[THING]='Y','WHAT IF 1'[WHAT IF 1 VALUE],'WHAT IF 2'[WHAT IF 2 VALUE]).
Let me know if this works.
Thanks,
Tejaswi
I'm not sure what tejaswidmello's addition of MAXX will do to your measure, but I would use SELECTEDVALUE. The measure will only return a result when the context is down to a signle value for F[THING], like when you're iterating through a table visual, but that seems to be when you want it calculated anyway:
MeasureWithParams = F[AMOUNT]*IF(SELECTEDVALUE(F[THING])='Y','WHAT IF 1'[WHAT IF 1 VALUE],'WHAT IF 2'[WHAT IF 2 VALUE])
It gets trickier if you're trying to sum all the values this measure gives but can be done with SUMX:
Sum of Measure = SUMX(SUMMARIZE(ALL(F), F[THING], "MeasureW/Params", [MeasureWithParams]), [MeasureW/Params])
You may have to replace the ALL with ALLEXCEPT if you want other filters you have in place to apply to the sum.
I do need results from both true and false results from the IF statement, so that they can be aggregated (summed) together.
Here's what this would look like in SQL, if it helps:
select
sum(f.[AMOUNT] * case when f.[THING] = 'Y' then f.[WHAT IF 1 VALUE] else f.[WHAT IF 2 VALUE] end) as AGGREGATED_CALC
from DATA f
Can this be done in DAX?
Do you need just the one row's worth of true and false results, or the true/false results from the whole table to sum?
To sum both of them for a single row context, drop the IF altogether:
SumWhatif1Whatif2 = 'WHAT IF 1'[WHAT IF 1 VALUE] + 'WHAT IF 2'[WHAT IF 2 VALUE]
Honestly, most of this depends on what results you're trying to sum and how your what if measures are calculated. I'm giving super high level answers that are dependant on the details of your solution. Could you share a table (even a mock up in excel) of what inputs/outputs you expect from this?
As seen in my SQL, I need to aggregate the entire table's worth of of true/false results. I can't drop the if statement. I need some records to multiply by one factor, and other records to multiply by a different factor, depending on what the value is in F[THING].
The WHAT IFs are simply user inputed decimal numbers, so that a user can enter a value into WHAT IF 1 and affect those records, and a different value in WHAT IF 2 and affect those records. This is quite easy to accomplish in most other tools, so I'm confused why it isn't more straightforward here.
Here's a mockup of the data and what I need. Both WHAT IFs are user inputs, columns A and B are in the raw data, and the formula in column C is as follows: =AMOUNT*IF(THING="Y",WHAT IF 1,WHAT IF 2).
THING | AMOUNT | NEW MEASURE | WHAT IF 1 | WHAT IF 2 | ||
Y | 30 | 24 | 0.8 | 0.25 | ||
N | 20 | 5 | ||||
Y | 60 | 48 | ||||
Y | 1500 | 1200 | ||||
N | 70 | 17.5 | ||||
1294.5 | <--- This is what I need |
Ahhhh, I see. I didn't understand your problem before. The table of expected results was very helpful. I thought you had some requirement where you would want both the result of the [Amount] * [What if 1] and the result of [Amount] * [What if 2] added into the final total.
You want to use SUMX.
SUMX(Table, Table[Amount] * IF(Table[THING]="Y", [WHAT IF 1], [WHAT IF 2]))
edit: I got rid of the error that I introduced when I added x on [AMOUNT] by fixing my oversight in the sumx formula. It still errors on [THING], seemingly requiring [THING] to be an aggregate (min, max, etc.).
Thoughts?
Ah, the way you were writing it, I thought [THING] was a measure, like [AMOUNT]
If it's actually in a table, just use SELECTEDVALUE([THING]) to "aggregate" the one value you have at a time.
October was a busy month in the community. Read the recap article to learn about some of the events and content.
Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.
Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.
User | Count |
---|---|
69 | |
67 | |
58 | |
58 | |
56 |