Question about nested IFs

[Anonymous]

Hi Everyone,

I have a table called 'Brands' and another table called 'Brands + Others', both the tables have a column called 'Brand'.

I have a slicer on the page that is connected to 'Brands' table and the visual on the page is connected to 'Brands + Others'.

But there are some brands that have sub brands, so for example there is a brand A that has sub brands like brand a, brand b, brand c...

Here is the code that I am trying to implement:

VAR selectedbrands = 
IF(INTERSECT(VALUES(Brands[Brand]), VALUES('Brands + Others'[Brand]))= "Nike Combined",
        (INTERSECT(VALUES('Brands + Others'[Brand]), VALUES(Nike[Brand]))),
        IF(INTERSECT(VALUES(Brands[Brand]), VALUES('Brands + Others'[Brand])) = "Adidas Combined", 
            (INTERSECT(VALUES('Brands + Others'[Brand]), VALUES(Adidas[Brand]))) ,
            IF(INTERSECT(VALUES(Brands[Brand]), VALUES('Brands + Others'[Brand])) = "NB Combined", 
                (INTERSECT(VALUES('Brands + Others'[Brand]), VALUES(NB[Brand]))),
                INTERSECT(VALUES(Brands[Brand]), VALUES('Brands + Others'[Brand]))
            )
        )
    )

I have created seperate tables for Nike, Adidas and NB with their respective sub brands in a column called 'brand' and I have inserted the values 'Nike combined', "Adidas Combined' and 'NB Combined' in the 'Brands' and 'Brands + Others' tables, so for example if the user want to see the visaul with the a brand and its sub brands combined he can select for example Nike Combined and the visual will show the sales of all the nike brands, but in case user wants to see only the sales of a particular Nike sub brand they can also select that from the slicer. 

Howevr, I am getting the following error when I try to run my nested IF DAX formula:

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.

 Can someone please suggest any solutions or if any explanation is required related to this problem, please let me know, its a little hard to explain this challenge.

Thanks. 

[AlexisOlson · Accepted solution]

Your formula would be more readable as a SWITCH statement like this

selectedbrands =
VAR _Brands = VALUES ( Brands[Brand] )
VAR _Others = VALUES ( 'Brands + Others'[Brand] )
VAR _Nike   = VALUES ( Nike[Brand] )
VAR _Adidas = VALUES ( Adidas[Brand] )
VAR _NB     = VALUES ( NB[Brand] )
RETURN
    SWITCH (
        INTERSECT ( _Brands, _Others ),
        "Nike Combined",   INTERSECT ( _Others, _Nike   ),
        "Adidas Combined", INTERSECT ( _Others, _Adidas ),
        "NB Combined",     INTERSECT ( _Others, _NB     ),
        INTERSECT ( _Brands, _Others )
    )

However, there are still problems with the above.

1. The intersection of two columns might not be a single value.
2. IF cannot output a table or list, only a single scalar value.

The error message you're getting typically comes from using something as a filter argument in CALCULATE that doesn't specify a column to filter. I don't think your IF function is causing this specific error itself, only if you're using this variable in CALCULATE. It fails in CALCULATE since the data lineage is ambiguous. This specific error message could likely be resolved using TREATAS. You'd still likely have other issues though.

In any case, it probably isn't the best choice to have separate tables for each brand. You can likely achieve what you're ultimately after by appending all of the brand tables into one larger table. Without seeing your model, it's difficult to be more specific though.