Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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])
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |