Forum Discussion
If dimension in calculated measure
Everything is in the same table except the "what ifs", since BI put them in their own respective tables, but it still gives me the error that [THING] can't be found.
Resident RockstarCan you share the actual expression you're trying to use? There's something up with your syntax that I'm not getting.
The exact text of the error message would be helpful too.
- New Sale = sumx(F,F[AMOUNT]*if(SELECTEDVALUE(F[THING])="Y",'WHAT IF 1'[WHAT IF - 1 Value],'WHAT IF 2'[WHAT IF 2 Value]))As of right now, this doesn't return an error, but it only works if I filter the visual to one of the two possible values in F[THING]. When combined, only the [WHAT IF 2 VALUE] works. I'm guessing that's to do with the SELECTEDVALUE function, but I'm only beginning to learn DAX.
- Cmcmahan
Yeah, you're 100% right. SELECTEDVALUE only returns a value if there's one option available. Otherwise, it returns a blank (or a default you can set).
I (for some reason) thought that within each visual row category, all the rows in F had the same WhatIf flag, which made this much easier in my head. You get to do something real funky now, because you want to get the total amount * weight1 where whatif 1 and the total amount * weight2 where whatif 2.
Best way I know how to do this is to summarize, and then sumX the summarize:
ActualWorkingSumForRealThisTime = SUMX(
SUMMARIZE(
F, F[ID], F[THING], //F[ID] is whatever value is unique to the row, whether that's an ID, index, name, etc. If there is no unique key, add one to the table.
"WHAT IF Weight", IF(F[THING] = "Y", RELATED('WHAT IF 1'[WHAT IF - 1 Value]), RELATED('WHAT IF 2'[WHAT IF 2 Value])),
"WeightedAmount", SUM(F[Amount]) * [WHAT IF Weight]
),
SUM([WeightedAmount])
)You may need to use SELECTEDVALUE on the WHAT IF Values. If you don't have an ID, then you can still make this work, but only if the WHAT IF value is the same for every row with an Y and the same for every row with an N. Just remove F[ID] and group by THING only.
If this doesn't work, could you share your relationship diagram of the tables involved here? Or an anonymized sample set of data that demonstrates the issue? It would help to know where everything is sitting in relation to one another.
This is a very simple single flat table. The only things that don't reside within that table are the two WHAT IFs, since it seems those have to reside in their own "tables" (please correct me if there's a better way to do this). There is no unique value on each row, unfortunately.
- Cmcmahan
But in essence, the What if values are static weights, and which weight you use depends on the value of F[THING], which is indicated for each row of data, correct? The above solution should work for that setup.