Combining Filtering conditional on an IF statement

[Anonymous]

I am trying to create a new measure whereby you apply a calculation to a filtered version of the table, based on an if statment of a different column.

A simplified version of the table is shown below:

Category	Year	Value
A	2018	11
A	2019	12
A	2020	14
B	2018	9
B	2019	9
B	2020	19

What I am trying to do is have a conditional filtering, so that when Category == "A", then we filter to Years == 2018 or 2019 and sum Value, but if Category = B, then we filter to Year == 2019 or 2020, and sum Value.

I currently have a dax formula that looks like this:

Measure = CALCULATE(SUM(Sheet1[Value]), 
                    IF(Sheet1[Category] == "A", 
                    Sheet1[Year] == 2018 || Sheet1[Year] == 2019, 
                    Sheet1[Year] == 2019 || Sheet1[Year] == 2020))

However, I am getting the error "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

Could anyone help with how best to tackle this problem?

[Cmcmahan]

Your original problem is that you're trying to test whether [Category] = "A".  The problem is that [Category] is a column, not a single value.  And when creating measures, PowerBI doesn't know that you plan to use this only in circumstances where the context has been filtered to a single category.  You can use an aggregation to change the value of [Category] from a column of values to a single value.  My preferred option is SELECTEDVALUE, since it will work when you have multiple rows of data, but they all have the same value in the given column. You can also default the result, if there's no values, or if you get multiple values.  The default for this is BLANK().

For minimal changes to your original measure, you can use this. New updates are in blue.

SimilarMeasure =
CALCULATE (
    SUM ( Sheet1[Value] ),
    FILTER (
        Sheet1,
        IF (
            SELECTEDVALUE ( Sheet1[Category] ) == "A",
            Sheet1[Year] == 2018 || Sheet1[Year] == 2019,
            Sheet1[Year] == 2019 || Sheet1[Year] == 2020
        )
    )
)

For a more robust measure, I would use SWITCH, since you can add more category options without a whole series of nested IFs.

SwitchMeasure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        Table1,
        SWITCH (
            SELECTEDVALUE ( Table1[Category] ),
            "A", Table1[Year] == 2018 || Table1[Year] == 2019,
            "B", Table1[Year] == 2019 || Table1[Year] == 2020, //Add more categories in this format
            TRUE()  //This is the else expression, and will return all values if SELECTEDVALUE([Category]) is not A or B,
                    // e.g. in a total row of a table
        )
    )
)

EDIT: just saw that @AlB had a very similar method.  The SUMX version will also work, and only sum each row if it meets your condition.  The downside is that it has worse performance than SUM, since it has to re-evalute each row individually instead of filtering the entire table, but is likely the cleanest way to write this measure if you have a large number of categories.

[RobbeVL]

Hi,

What is it you are exacly trying to calculate here?
Could you specify ? 

Robbe

[Anonymous]

The result would look like:

Category	Year	Value	Measure
A	2018	11	11
A	2019	12	12
A	2020	14
B	2018	9
B	2019	9	9
B	2020	19	19

Or if we didnt include year or value in the table in the table:

Category	Measure
A	23
B	28

[AlB · Accepted solution]

Hi @Anonymous 

It is not clear what you need.

If the measure is to add up everything with those rules: 

Measure =
SUMX (
    Sheet1,
    SWITCH (
        TRUE (),
        Sheet1[Category] = "A" && Sheet1[Year] IN { 2018, 2019 }, Sheet1[Value],
        Sheet1[Category] = "B" && Sheet1[Year] IN { 2019, 2020 }, Sheet1[Value],
        0
    )
)