Forum Discussion
Filter column only if variable is not blank
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:
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) ) )
8 Replies
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.
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?
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.
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) ) )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.
