Forum Discussion
Slicer For Material Criteria From Different Columns
There will be the 6 columns...Just the ones with the 'Y' and 'N' values..
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.