DAX if not filtered, insert filter

[Conance-steven]

Some help needed for a DAX statement.

I have a table with business units, a table containing text on monthly actuals, and a one-cell table visual in which I want the text to show related to the corresponding business unit.

The logic is:

• if one business unit filtered is, output is the corresponding text
• if more than 1 is filtered, output should be a textual phrase "select one business unit"
• if nothing is filtered, DAX should treat this as if "Overall" is selected as a filter, and return the corresponding text.

So far I've managed to write the statement working for logic 1 and 2. For nr 3 I get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". 

My statement:   Text=
IF (
    ISFILTERED ( Businessunit[BU] );
    IF (
        HASONEFILTER ( Businessunit[BU] );
        DISTINCT ( Comments[text] );
        "select one business unit"
    );
    CALCULATE ( FILTER ( Businessunit; Businessunit[BU] = "Overall" ) )
)

Please help 🙂  

[OwenAuger · Accepted solution]

Hi @Conance-steven 

The problem is in the final CALCULATE: the first argument of CALCULATE needs to be the expression to return, and the subsequent argument(s) are filters to apply.

Something like the below code should work.

It's probably not necessary in your model, but in the below code I also changed DISTINCT to SELECTEDVALUE, as it's a little safer. SELECTEDVALUE always returns a single value if there is only one value in the specified column, otherwise blank (or an optional second argument). 

Text =
IF (
    ISFILTERED ( Businessunit[BU] );
    IF (
        HASONEFILTER ( Businessunit[BU] );
        SELECTEDVALUE ( Comments[text] );
        "select one business unit"
    );
    CALCULATE ( SELECTEDVALUE ( Comments[text] ); Businessunit[BU] = "Overall" )
)

 Regards

Owen

[Conance-steven]

Thanks Owen. Works like a charm!