May 14, 2018 at 8:07 pm #9753
- Started: 1
- Replies: 0
- Total: 1
Thank you! New here
I guess this has been asked many times, but I can’t find the answer. I’ve found information about the use of IF statements in measures, but I haven’t been able to figure out which problem I have. Here the scenario.
I have a table with a few columns: Type; Budget, Ceiling. I need to create a measure named “Approved” with a value from either Budget or Ceiling based on the value in Type. In a pseudo SQL statement I’d have something like this:
IF [Type]=”Confirmed” THEN [Budget] ELSE [Ceiling].
This is what I have in DAX:
The Check Formula button highlights the second [Type] and says “The value for ‘Type’ cannot be determined. Either ‘Type’ doesn’t exist, or there is no current row for a column named ‘Type’.
I believe it has to do something with aggregation but I can’t figure out.
NB. I know the book Power Pivot and Power BI: The Excel User’s Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016 discusses IF statements, and I took a look, but I’m stuck anyhow.May 14, 2018 at 10:40 pm #9754
- Started: 7
- Replies: 2552
- Total: 2559
The second use of [Type] needs to be enclosed within a VALUES () function and the column name should be preceded by the table name. Something like this:
Approved := IF ( HASONEVALUE ( TableNameGoesHere[Type] ), IF ( VALUES ( TableNameGoesHere[Type] ) = "Confirmed", SUM ( TableNameGoesHere[Budget] ), SUM ( TableNameGoesHere[Ceiling] ) ) )
Depending on what you are trying to achieve, you might try the following and see if makes sense in your case:
Approved := SUMX ( TableNameGoesHere, IF ( VALUES ( TableNameGoesHere[Type] ) = "Confirmed", TableNameGoesHere[Budget], TableNameGoesHere[Ceiling] ) )
You must be logged in to reply to this topic.