Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

If dimension in calculated measure

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?

 

 

17 REPLIES 17
Cmcmahan
Resident Rockstar
Resident Rockstar

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. 

Anonymous
Not applicable

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?

Anonymous
Not applicable

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). 

THINGAMOUNTNEW MEASURE  WHAT IF 1WHAT IF 2
Y3024  0.80.25
N205    
Y6048    
Y15001200    
N7017.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]))
Anonymous
Not applicable

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.

Anonymous
Not applicable

If I'm following along correctly, this brings us back to a result that only works when one value or the other is filtered/selected from the table, whereas I need the total aggregated amount - as seen in my example table. To clarify, the table is not how the results need to be displayed, but rather the end result, which is the column total at the bottom labeled "this is what I need".

Not if it's wrapped in a SUMX expression.  SUMX evaluates the expression  ([Amount] * [Appropriate WHAT IF]) for each row of the given table. As long as [THING] is in the table being evaluated, this should work.  

 

If it's in a different table, you may need to use RELATED instead.  A lot of this depends on how your data model is set up.

Anonymous
Not applicable

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.

Can 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.

Anonymous
Not applicable

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. 

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.

Anonymous
Not applicable

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. 

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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

Hi @Anonymous 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.