Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |