Filter column only if variable is not blank

[shadowsong42]

I'm trying to filter a text column based on a variable, but only if that variable is not blank. Here's my current code:

Actual Sell Thru (Channel) =

VAR EquivalentStoresSegment =

    SWITCH(

        TRUE(),

        VALUES('Product'[Is Retail Or Commercial SKU]) = "Retail"

            && VALUES('Product'[Is Retail Or Commercial SKU]) <> "Commercial",

            "Stores Consumer",

        VALUES('Product'[Is Retail Or Commercial SKU]) = "Commercial"

            && VALUES('Product'[Is Retail Or Commercial SKU]) <> "Retail",

            "Stores Commercial",

        BLANK()

    )

RETURN

CALCULATE(

    [Actuals Sell Thru (Units)],

    REMOVEFILTERS('Product'[Is Retail Or Commercial SKU]),

    // Only apply the equivalent Stores filter if a mapping was found

    IF(

        NOT ISBLANK(EquivalentStoresSegment),

        'Business'[Stores Surface Segment]=EquivalentStoresSegment

    )

)

However, I get an error on the last line - "Cannot find name '[Stores Surface Segment]'".

My guess is that's because this is a text column, not a measure. If I change that last line to

FILTER(Business,Business[Stores Surface Segment]=EquivalentStoresSegment)

I no longer get the error on the name, but instead I get one that says "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

How do I filter this text column only if EquivalentStoresSegment is not blank?

[v-nmadadi-msft]

Hi @shadowsong42 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[shadowsong42 · Accepted solution]

I figured it out.
Instead of the "switch values=a and values<>b, a1, values=b and values<>a, b1, else blank" code, i needed to do "if single value selected, then switch value=a, a1, value=b, b1; else blank"
And instead of "calculate column, if not isblank, filter", i needed to do "if isblank, column; else calculate column, filter"

Here's the code:

Actual Sell Thru (Channel) =
VAR EquivalentStoresSegment = 
    IF(COUNTROWS(VALUES('Product'[Is Retail Or Commercial SKU]))=1,
        SWITCH(TRUE(),
            VALUES('Product'[Is Retail Or Commercial SKU]) = "Retail", 
                "Stores Consumer",
            VALUES('Product'[Is Retail Or Commercial SKU]) = "Commercial" ,
                "Stores Commercial"
        ),
        BLANK()
    )
RETURN
IF(ISBLANK(EquivalentStoresSegment),
    [Actuals Sell Thru (Units)],
    CALCULATE([Actuals Sell Thru (Units)],
        REMOVEFILTERS('Product'[Is Retail Or Commercial SKU]),
        KEEPFILTERS('Business'[Stores Surface Segment]=EquivalentStoresSegment)
    )
)

[lbendlin]

 VALUES('Product'[Is Retail Or Commercial SKU]) = "Retail"
            && VALUES('Product'[Is Retail Or Commercial SKU]) <> "Commercial",

What is this supposed to achieve?

IF(
        NOT ISBLANK(EquivalentStoresSegment),
        'Business'[Stores Surface Segment]=EquivalentStoresSegment
    )

You cannot use an assignment operation when a value is expected (unless you want to return true/false?)

If you want to use a column you need to provide an aggregation function.

[shadowsong42]

The first snippet: 

My report has a multi-select slicer for "Is Retail or Commercial". If the slicer has Retail selected and Commercial not selected, set the variable to Stores Consumer. If the opposite, set to Stores Commercial. For any other combination of selections in the slicer, set the variable to blank.

Second snippet: 

I don't know what an assignment operation is. 

There are three possible values for the EquivalentStoresSegment variable: Stores Consumer, Stores Commercial, or blank. If it's blank, do not filter Stores Surface Segment. Otherwise filter Stores Surface Segment where it is equal to EquivalentStoresSegment.

Does that answer your questions?

[MarkLaf]

From your OP, it sort of looks like you are trying to implement a virtual relationship between Product and Business (or Business/Product to whatever tables are involved in measure [Actuals Sell Thru (Units)])?

If I understand correctly, and you have all your relationships set up already (i.e., no need for virtual relationship), a check that could work:

VAR _valsSelected = ALLSELECTED( 'Product'[Is Retail Or Commercial SKU] ) 
VAR _valCheck = 
    ISEMPTY( EXCEPT( _valsSelected, { "Commercial" } ) ) 
    || ISEMPTY( EXCEPT( _valsSelected, { "Retail" } ) )

If you want to implement in a calculation, it would look something like:

Measure Hide When Multi-Selected = 
VAR _valsSelected = ALLSELECTED( 'Product'[Is Retail Or Commercial SKU] ) 
VAR _valCheck = 
    ISEMPTY( EXCEPT( _valsSelected, { "Commercial" } ) ) 
    || ISEMPTY( EXCEPT( _valsSelected, { "Retail" } ) )
RETURN
CALCULATE( [Actuals Sell Thru (Units)], FILTER( Business, _valCheck ) )

Explanation, when _valCheck = TRUE, the filter from Business on [Actuals Sell Thru (Units)] behaves normally. When _valCheck = FALSE, then all Business rows that would normally be in the filter context now get removed.

You can similarly set this up in a measure for filter purposes:

Measure Filter Hide When Multi-Selected = 
VAR _valsSelected = ALLSELECTED( 'Product'[Is Retail Or Commercial SKU] ) 
VAR _valCheck = 
    ISEMPTY( EXCEPT( _valsSelected, { "Commercial" } ) ) 
    || ISEMPTY( EXCEPT( _valsSelected, { "Retail" } ) )
RETURN
IF( _valCheck, 1 )

Put this in the filter well of a visual and set to 'is not blank'.

Quick gif showing the above with some test data I threw together.

[shadowsong42]

The relationships don't already exist. I have a whole set of fact tables, most of which are filtered by Is Retail Or Commercial via a relationship to the Product table. The fact table containing Actuals, on the other hand, is filtered by Stores Surface Segment via a relationship to the Business table. There is no valid relationship between Business and Product.

Functionally, I want to filter on Retail vs Commercial, whether I use Is Retail or Commercial SKU or Stores Surface Segment to do it. I want to have a single slicer to select Retail vs Commercial, which will filter the fact tables based on Is Retail or Commercial for all fields except Actuals.

For Actuals, I want it to filter based on the Stores Surface Segment value that is equivalent to the selected Retail vs Commercial slicer value. If the Retail vs Commercial slicer has both or neither value checked, do not filter Stores Surface segment.

The fact that there is no valid relationship between Business and Product is the core of my problem, and is why I'm trying to write a calculated measure to set up an equivalency.

[MarkLaf]

Provide some representative dummy data, and people can provide a more specific solution.

Note that I would consider getting the 'only apply filter when one value is selected in a silcer' behavior as a separate issue from getting a physical or virtual relationship working. You should do the latter first, and then build the former on top of it.

From what I'm understanding, you have a few options for setting up the relationship you need:

1) You can add a calculated column to Actuals, pulling over RELATED Business[Stores Surface Segment], converting to applicable Product[Is Retail or Commercial SKU] value, then relating Product[Is Retail or Commercial SKU] to Actuals[New calc column] with many-to-many, single filter direction (Product filters Actuals) relationship. Now, Product will filter Actuals. 

2) Create a bridge table between Product and Business, and leverage that relationship in a filter measure on a visual with Actuals, such that a Product[Is Retail or Commercial SKU] slicer will impact the visual.  See https://learn.microsoft.com/en-us/power-bi/guidance/star-schema#factless-fact-tables.

To get really in the weeds, check out the following (translating their example to yours, I think: Business = Customer, Product = Sport, Actuals = Sales, CustomerSport is the bridge table you would want to create): https://www.sqlbi.com/articles/different-options-to-model-many-to-many-relationships-in-power-bi-and...

3) Set up a virtual relationship (preferably with TREATAS). From what I can tell, this is what you are trying to do in the measure you shared up top. See https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/ 

Example of what this might look like:

Actual Sell Thru (Channel) = 
VAR _prodBizEquivs = 
SUMMARIZE( 
    SUMMARIZECOLUMNS( 
        Product[Is Retail Or Commercial SKU], 
        "BizEquiv", SWITCH( 
            SELECTEDVALUE( Product[Is Retail Or Commercial SKU] ), 
            "Retail", "Stores Consumer", 
            "Commercial" , "Stores Commercial" 
        ) 
    ), 
    [BizEquiv] 
)
RETURN
CALCULATE( 
    [Actuals Sell Thru], 
    TREATAS( 
        _prodBizEquivs, 
        Business[Stores Surface Segment] 
    ),
    Business
)

In the above, [Acutals Sell Thru] is just a "normal" agg, e.g.

Actuals Sell Thru = SUM( Actuals[Revenue] )

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...