Home Forums Power Pivot IF statement in measure

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

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #9753

    lguzman
    Participant
    • 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(HASONEVALUE([Type]),
    IF ([Type]=”Confirmed”,
    SUM([Budget]),
    SUM([Ceiling]),
    0)

    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

    lg

    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.

     

     

    #9754

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    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]
        )
    )
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.