Slicer For Material Criteria From Different Columns

[Anonymous]

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

[darentengmfs]

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.

[Anonymous]

Hi, If I unpivot then wont that create more records in my table (which is big)?

[darentengmfs]

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]

Hi darentengmfs, Actually I have about 35 columns and over 200,000 records at this time, and the record count builds everyday.

[darentengmfs]

@Anonymous 

Are all 35 columns filled with Y/N and you want to have a slicer with all 35 columns?

[Anonymous]

Oh no! Some are vrchar and some are numeric.

[darentengmfs]

@Anonymous 

How many columns is needed for this slicer?

[Anonymous]

There will be the 6 columns...Just the ones with the 'Y' and 'N' values..

[darentengmfs]

@Anonymous 

Will the same Material appear twice or more?

If you don't mind, please share an example of your dataset.

[Anonymous]

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.

1. Duplicate your primary table, remove all columns besides the Material column and all the Y/N columns.
2. Select all columns in the duplicate table BESIDES Material column
3. 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.

[Anonymous]

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.