Forum Discussion

Conance-steven's avatar
Conance-steven
Frequent Visitor
6 years ago
Solved

DAX if not filtered, insert filter

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's avatar
    OwenAuger
    6 years ago

    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

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    6 years ago

    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