Forum Discussion
shadowsong42
Resolver I
1 year agoFilter 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: Actual Sell Thru (Channel) = VAR EquivalentStoresSegment = SWITCH( ...
- 1 year ago
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) ) )
shadowsong42
Resolver I
1 year agoI 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)
)
)