Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Conance-steven
Frequent Visitor

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 🙂  

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

Thanks Owen. Works like a charm!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.