Forum Discussion
Using SWITCH within KEEPFILTERS and IN?
Is it possible to switch out the list inside of a KEEPFILTERS IN argument?
I can't seem to get it to work.
I want my end user to be able to make a parameter selection, and that selection changes the list that the KEEPFILTERS statement looks at after the IN.
This version even keeps the "KeepFilters" in place:
VAR _UserSelection= [Selected Conversion Type] VAR _Table = CALCULATETABLE( SUMMARIZE( b_Assortment_Bridge, b_Assortment_Bridge[Inventory_Key], b_Assortment_Bridge[Assortment StdCost Ratio], b_Assortment_Bridge[Assortment Flag] ), KEEPFILTERS( SWITCH( _UserSelection, "Both", b_Assortment_Bridge[Assortment Flag] IN {"", "A", "B"}, "A Only", b_Assortment_Bridge[Assortment Flag] IN {"", "A"}, "B Only", b_Assortment_Bridge[Assortment Flag] IN {"", "B"}, "Off", b_Assortment_Bridge[Assortment Flag] IN {""} ) ) ) VAR _Measure = SUMX( _Table, CALCULATE( [.Sales - Selling SKUs] ) * [Assortment StdCost Ratio] ) RETURN _Measure
4 Replies
This was the cleanest I could make the code and have it still work as expected.
This version even keeps the "KeepFilters" in place:
VAR _UserSelection= [Selected Conversion Type] VAR _Table = CALCULATETABLE( SUMMARIZE( b_Assortment_Bridge, b_Assortment_Bridge[Inventory_Key], b_Assortment_Bridge[Assortment StdCost Ratio], b_Assortment_Bridge[Assortment Flag] ), KEEPFILTERS( SWITCH( _UserSelection, "Both", b_Assortment_Bridge[Assortment Flag] IN {"", "A", "B"}, "A Only", b_Assortment_Bridge[Assortment Flag] IN {"", "A"}, "B Only", b_Assortment_Bridge[Assortment Flag] IN {"", "B"}, "Off", b_Assortment_Bridge[Assortment Flag] IN {""} ) ) ) VAR _Measure = SUMX( _Table, CALCULATE( [.Sales - Selling SKUs] ) * [Assortment StdCost Ratio] ) RETURN _Measure
Not surprising _SwitchList doesn't work because it returns a string whereas IN operator requires a table value; btw, IN is the syntax sugar for CONTAINSROW() function.
Can't get any useful info on data model itself; the only advice I can offer is to use a slicer like this,
... IN Values(OPT[Type])Thank you for your reply. I like your solution, except I want a default setting if they choose nothing. I've gotten it to work my way, but modified from my original attempt.
Btw, I thought _SwitchList would be returning a list/single column table, not a string. Clearly I was wrong.
Thank you again.
