DAX using IF and AND functions

Question

I'm trying to create a trending table with a value and a forecast. The forecast needs to start from the current month going forward. I am using this dax function:

Spend Forecast =
IF (
    OR (
        DIVIDE (
            CALCULATE (
                SUM ( refv_Spend_Cap[Spend_2019] ),
                FILTER ( refv_Spend_Cap, refv_Spend_Cap[Split] = "Spend Actual" )
            ),
            1000000
        )
            < 1,
        DIVIDE (
            CALCULATE (
                SUM ( refv_Spend_Cap[Spend_2019] ),
                FILTER ( refv_Spend_Cap, refv_Spend_Cap[Ind_Monthend] = "x" )
            ),
            1000000
        )
            < 1
    ),
    DIVIDE (
        CALCULATE (
            SUM ( refv_Spend_Cap[Spend_2019] ),
            FILTER ( refv_Spend_Cap, refv_Spend_Cap[Split] = "Spend Forecast" )
        ),
        1000000
    ),
    ""
)

The formula is calculating to check if these two conditions are met: if there's no value then populate the forecast or if the ind monthend = 'x' then it should populate, if those two conditions are not met then it should leave it blank. There are no syntax errors on the query but i am getting this error:

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column

Where did I go wrong?

RADO RADO · Accepted Answer · 2018-07-18T08:15:22

It's very hard to comprehend such long formulas. It's the best practice to break them down into multiple measures. For example, your code can be re-writen as follows:

Create base measure:

Total Spend = SUM ( refv_Spend_Cap[Spend_2019] ) / 1000000

Now re-use the base measure to create 3 conditional measures. No need to use FILTER here:

Spend Actual = CALCULATE ( [Total Spend], refv_Spend_Cap[Split] = "Spend Actual" )

Spend X = CALCULATE ( [Total Spend], refv_Spend_Cap[Ind_Monthend] = "x" )

Spend Forecast = CALCULATE ( [Total Spend], refv_Spend_Cap[Split] = "Spend Forecast" )

Then the final result is simply:

Forecast = IF ( [Spend Actual] < 1 || [Spend X] < 1, [Spend Forecast], "")

It's much easire to understand what's happening, and easier to debug. You will also gain perfomance bonus because (usually) re-used measures are cached and calculated only once.

Try this code, if it still gives you problems, describe the new error and I'll help you fix it.

BTW, there is a popular free tool to format your DAX code: Dax Formatter

DAX using IF and AND functions

1 Answers