The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
246 | |
119 | |
114 | |
86 | |
70 |