Forum Discussion
Slicer For Material Criteria From Different Columns
Post ProdigyAnonymous
Will the same Material appear twice or more?
If you don't mind, please share an example of your dataset.
So the same material can only show once in a group but can show as I stated earlier in up to 3 or 4 different groups.
Heres a sample data set..
| Workcenter | FiscalYear | PlanMonth | EndofMonth | Date | Plan | DailyActual | MtdActual | FYtdActual | MtdAccmp | PlanDays | ElapsedDays | UnitsRemaning | RemDailyAvgReq | MtdPlanned | MtdDailyAvg | DailyGoal | MtdDLU | MtdNvc | DailyDLU | DailyNvc | DayType | ViewType | SupplyCommit | Skincare | EOM | Weekof | WeekendActual | SaturdayActual | SundayActual | MonthlyAttainment | WkcntrRes | ReportingType | BatchRecord | Workcenter2 | ResourceMade | ResourcePlanned | Material | Shift | UOM | SkincareExclude | Brand | BrandLetter | Category | IsFillAssembly | IsSkincare | IsSupplyCommit | IsSkinCareSupplyCommit | IsTubes | IsFragrance | ISPlan | Kettle | Lab | 6_7_Day | SkincareSupplyCommitLine |
| Tubes | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 44500 | 0 | 33216 | 0 | 0 | 21 | 1 | 11284 | 564 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0.662752 | NULL | MATERIAL | 2020091048E01L000M1051000PLAN | Tubes | NA | M1051000 | 048E01L000 | 0 | EA | NULL | ORGS | OR | NULL | Y | N | Y | Y | Y | N | Y | NULL | NULL | NULL | YES |
| Tubes | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 128336 | 0 | 0 | 0 | 0 | 21 | 1 | 128336 | 6416 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0.662752 | NULL | MATERIAL | 2020091048E01L000M1063000PLAN | Tubes | NA | M1063000 | 048E01L000 | 0 | EA | NULL | ORGS | OR | NULL | Y | N | Y | Y | Y | N | Y | NULL | NULL | NULL | YES |
| West Lotions | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 9115 | 0 | 9758 | 0 | 0 | 21 | 1 | -643 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0 | NULL | MATERIAL | 20200910730031000M1007000PLAN | Total Skincare Supply Commit Lines | NA | M1007000 | 7.3E+08 | 0 | EA | N | ESLAD | ESL | Skincare | Y | Y | Y | Y | N | N | Y | NULL | NULL | NULL | YES |
| West Lotions | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 15000 | 0 | 0 | 0 | 0 | 21 | 1 | 15000 | 750 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0 | NULL | MATERIAL | 20200910730031000M1009000PLAN | Total Skincare Supply Commit Lines | NA | M1009000 | 7.3E+08 | 0 | EA | N | ESLAD | ESL | Skincare | Y | Y | Y | Y | N | N | Y | NULL | NULL | NULL | YES |
| Fragrance | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 6368 | 0 | 0 | 0 | 0 | 21 | 1 | 6368 | 318 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 1 | NULL | 00:00.0 | 0 | 0 | 0 | 0.48941 | NULL | MATERIAL | 20200910943010471M1033000PLAN | Fragrance | NA | M1033000 | 9.43E+08 | 0 | EA | NULL | ESLAD | ESL | NULL | Y | N | N | N | N | N | Y | NULL | NULL | NULL | NO |
| Tubes | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 4033 | 0 | 4158 | 0 | 0 | 21 | 1 | -125 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0.662752 | NULL | MATERIAL | 20200910ATX010000M1062000PLAN | Tubes | NA | M1062000 | 0ATX010000 | 0 | EA | NULL | ORGS | OR | NULL | Y | N | Y | Y | Y | N | Y | NULL | NULL | NULL | YES |
| East Lotions | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 12700 | 0 | 0 | 0 | 0 | 21 | 1 | 12700 | 635 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0 | NULL | MATERIAL | 20200910EEP010000M1025000PLAN | East Lotions | NA | M1025000 | 0EEP010000 | 0 | EA | NULL | ORGS | OR | NULL | Y | Y | Y | Y | N | N | Y | NULL | NULL | NULL | YES |
| East Lotions | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 22870 | 0 | 17902 | 0 | 0 | 21 | 1 | 4968 | 248 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0 | NULL | MATERIAL | 20200910GGM016000M1028000PLAN | East Lotions | NA | M1028000 | 0GGM016000 | 0 | EA | NULL | ORGS | OR | NULL | Y | Y | Y | Y | N | N | Y | NULL | NULL | NULL | YES |
| Creams | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 135075 | 0 | 102830 | 0 | 0 | 21 | 1 | 32245 | 1612 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0 | NULL | MATERIAL | 20200910GGM470475M1003FILPLAN | Creams | NA | M1003FIL | 0GGM470475 | 0 | EA | NULL | ORGS | OR | NULL | Y | Y | Y | Y | N | N | Y | NULL | NULL | NULL | YES |
| Creams | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 6349 | 0 | 6435 | 0 | 0 | 21 | 1 | -86 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0 | NULL | MATERIAL | 20200910GGM476476M1003FILPLAN | Creams | NA | M1003FIL | 0GGM476476 | 0 | EA | NULL | ORGS | OR | NULL | Y | Y | Y | Y | N | N | Y | NULL | NULL | NULL | YES |
| Tubes | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 40000 | 0 | 0 | 0 | 0 | 21 | 1 | 40000 | 2000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0.662752 | NULL | MATERIAL | 20200910GGM550000M1054000PLAN | Tubes | NA | M1054000 | 0GGM550000 | 0 | EA | NULL | ORGS | OR | NULL | Y | N | Y | Y | Y | N | Y | NULL | NULL | NULL | YES |
| Tubes | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 28000 | 0 | 0 | 0 | 0 | 21 | 1 | 28000 | 1400 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0.662752 | NULL | MATERIAL | 20200910GW1010000M1063000PLAN | Tubes | NA | M1063000 | 0GW1010000 | 0 | EA | NULL | ORGS | OR | NULL | Y | N | Y | Y | Y | N | Y | NULL | NULL | NULL | YES |
| Tubes | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 6700 | 0 | 0 | 0 | 0 | 21 | 1 | 6700 | 335 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0.662752 | NULL | MATERIAL | 20200910GWL010000M1062000PLAN | Tubes | NA | M1062000 | 0GWL010000 | 0 | EA | NULL | ORGS | OR | NULL | Y | N | Y | Y | Y | N | Y | NULL | NULL | NULL | YES |
| Tubes | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 4875 | 0 | 0 | 0 | 0 | 21 | 1 | 4875 | 243 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0.662752 | NULL | MATERIAL | 20200910HJX010000M1064000PLAN | Tubes | NA | M1064000 | 0HJX010000 | 0 | EA | NULL | ORGS | OR | NULL | Y | N | Y | Y | Y | N | Y | NULL | NULL | NULL | YES |
| Tubes | 2021 | 9/1/2020 | 9/30/2020 | 9/1/2020 | 2700 | 0 | 0 | 0 | 0 | 21 | 1 | 2700 | 135 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Normal | M | 0 | 0 | NULL | 00:00.0 | 0 | 0 | 0 | 0.662752 | NULL | MATERIAL | 20200910HJX011000M1064000PLAN | Tubes | NA | M1064000 | 0HJX011000 | 0 | EA | NULL | ORGS | OR | NULL | Y | N | Y | Y | Y | N | Y | NULL | NULL | NULL | YES |
- darentengmfs
Anonymous
Since the Material # only appears once in your primary table, this is what you could do which I just tested on dummy data.
- Duplicate your primary table, remove all columns besides the Material column and all the Y/N columns.
- Select all columns in the duplicate table BESIDES Material column
- Unpivot the table, then filter the Value to "Y"
Once you have the new table created and transformed, apply the query. Set the relationship of your original table and the new table you just created, and change the Cross Filter Direction to Both.
Once this is done, add whatever you need from your primary table to a visual. Use the Attribute column from the new table in your slicer. It should then filter accordingly.
SO the material will appear once per month per resource (could be 2 different resources) would that cause an issue?
- darentengmfs
Anonymous
If the same material will appear again every month, you need to create a unique column for it.
Since it appears every month, you could concatenate the Material # with the Date column, which then will give you a unique ID for the primary table. Use that to define the relationship between the primary and duplicated table, with the same Cross Filter Direction = Both.
This way, if Material # 1000ABC has a "Y" in InFillAssembly in January 2020, but on February 2020, it is "N", when you filter InFillAssembly="Y", you will only see data for #1000ABC that's in January.