Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All. Hoping I can get some help here. I've looked at different Q&A's for issues like mine but just don't seem to see any answers. I have Material codes that can fall into different categories. I created column names in my table for a 'Y' or 'N' which shows the categories the Material can be in. I need to allow the user to select a category and if that Material is in that category, have it show up in the table. I also need to have the user Select another category and have that same material show in that category as well if it is part of it.
So you see below, the first material code 048E01L0000 should show up in my matrix table with the IsFillAssembly group or the IsSupplyCommitGroup or the IsSkincareSupplyCommit group or IsTubes group (Anyplace where there is a 'Y' value.). I need to somehow get a slicer to allow me to do it. I want the user to select a category and the materials that are 'Y' in that category will show in the table matrix. Any help is greatly appreciated! Thanks
Hi @Anonymous
You might want to create new columns for this.
InFillAssembly_Exist = IF('Table'[InFillAssembly] = "Y",'Table'[Material],"")
This will fill the new column with Y's into the Material number, and N's with blanks.
On the other hand, you can unpivot the columns. Go to Power Query Editor, select all columns EXCEPT for Material, go to Transform tab, and select Unpivot columns. You will get something like this. Then, filter out all the "n", apply the changes, and use Attribute as your slicer.
Hi @Anonymous
It will, but I believe since there will only be 2-3 columns left, it will not take a very long time to load. Loading time depends on both rows and columns.
@Anonymous
Are all 35 columns filled with Y/N and you want to have a slicer with all 35 columns?
@Anonymous
How many columns is needed for this slicer?
There will be the 6 columns...Just the ones with the 'Y' and 'N' values..
@Anonymous
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 |
@Anonymous
Since the Material # only appears once in your primary table, this is what you could do which I just tested on dummy data.
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?
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |