Home Forums Power Pivot IF statement in measure

This topic contains 1 reply, has 2 voices, and was last updated by  tomallan 8 months, 1 week ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
  • #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:

    IF ([Type]=”Confirmed”,

    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.

    Thank you


    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.




    • 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 (
        IF (
            VALUES ( TableNameGoesHere[Type] ) = "Confirmed",
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.