cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dbro020 Frequent Visitor
Frequent Visitor

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
tejaswidmello Established Member
Established Member

Re: If dimension in calculated measure

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

Super User
Super User

Re: If dimension in calculated measure

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. 

Dbro020 Frequent Visitor
Frequent Visitor

Re: If dimension in calculated measure

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?

Super User
Super User

Re: If dimension in calculated measure

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?

Dbro020 Frequent Visitor
Frequent Visitor

Re: If dimension in calculated measure

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.

Dbro020 Frequent Visitor
Frequent Visitor

Re: If dimension in calculated measure

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 
Super User
Super User

Re: If dimension in calculated measure

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]))
Dbro020 Frequent Visitor
Frequent Visitor

Re: If dimension in calculated measure

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?

Super User
Super User

Re: If dimension in calculated measure

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.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 200 members 2,300 guests
Please welcome our newest community members: