Grouping column data for filter

[Anonymous]

Hi Guys

I need help grouping data to add to a filter, the column im trying to group is the item number as per below:

I have tried using the below switch statement, but this adds the 4 items to the category "Online" and it does not appear in "Warehouse" category, where those items are available both in "Online" and "Warehouse"

Stock = SWITCH(
TRUE(),
ICITEM[Item Number] In {"ACCE22304","ASCC22104","ASCC22204","ASCC22304"},"Online",
"Warehouse"
)

Any recommendations, that will allow grouping to a category called, "Online" while at the same time making sure that the category "Warehouse" still includes all item numbers.

[v-cherch-msft]

Hi @Anonymous

You may try to add a slicer table as below. Then you may create a test measure and drag it to visual level filter to get the table as requested.

test =
IF (
 HASONEVALUE ( 'Check Stock'[Check Stock] ),
 IF (
 SELECTEDVALUE ( 'Check Stock'[Check Stock] ) = "Online"
 && MAX ( 'Sample'[Item Number] ) IN { "ASCC22104", "ASCC22204" }
 || SELECTEDVALUE ( 'Check Stock'[Check Stock] ) = "Warehouse",
 1
 ),
 1
)

Regards,

Cherie

[petrovnikitamai]

try to add new table for mapping

like

mapping = 
var online = {"ACCE22304";"ASCC22104";"ASCC22204";"ASCC22304"}
return 
UNION(
    GENERATE(SELECTCOLUMNS({"Online"};"status";{"Online"});online);
    GENERATE(SELECTCOLUMNS({"Warehouse"};"status";{"Warehouse"});VALUES(ICITEM[Item Number])))

sometimes u can get a cycle error. in this way try to get list with unique Item Number values from other table (instead VALUES(ICITEM[Item Number]) use VALUES(OtherTable[Item Number]).

for each status list of values

And create relationships between your main table and mapping table by Item Number column

then u can use field "status' for filtering your main table

[affan]

Hi @Anonymous

Can you elaborate this a little more? As your data is not showing anything in regards to Online or Warehouse

Regards

Affan

[Anonymous]

Hi Affan

Please read through my problem again, ill be using the categories i create "Online" and "Warehouse" in a filter like this:

I need to group column data, example "ASCC22104" and "ASCC22204" grouped as "Online", so i can use filter to only see those 2 items.

The 2 items should still be included in "Warehouse" which includes all items.