cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Slicer For Material Criteria From Different Columns

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

 

mmanisca_0-1600711228034.png

 

13 REPLIES 13
Super User I
Super User I

Hi @mmanisca 

 

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.

 

darentengmfs_0-1600716234878.png

 

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

Hi @mmanisca 

 

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.

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

@mmanisca 

 

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

Oh no! Some are vrchar and some are numeric.

@mmanisca 

 

How many columns is needed for this slicer?

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

 

mmanisca_0-1600718884564.png

 

@mmanisca 

 

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..

 

WorkcenterFiscalYearPlanMonthEndofMonthDatePlanDailyActualMtdActualFYtdActualMtdAccmpPlanDaysElapsedDaysUnitsRemaningRemDailyAvgReqMtdPlannedMtdDailyAvgDailyGoalMtdDLUMtdNvcDailyDLUDailyNvcDayTypeViewTypeSupplyCommitSkincareEOMWeekofWeekendActualSaturdayActualSundayActualMonthlyAttainmentWkcntrResReportingTypeBatchRecordWorkcenter2ResourceMadeResourcePlannedMaterialShiftUOMSkincareExcludeBrandBrandLetterCategoryIsFillAssemblyIsSkincareIsSupplyCommitIsSkinCareSupplyCommitIsTubesIsFragranceISPlanKettleLab6_7_DaySkincareSupplyCommitLine
Tubes20219/1/20209/30/20209/1/20204450003321600211112845640000000NormalM00NULL00:00.00000.662752NULLMATERIAL2020091048E01L000M1051000PLANTubesNAM1051000048E01L0000EANULLORGSORNULLYNYYYNYNULLNULLNULLYES
Tubes20219/1/20209/30/20209/1/2020128336000021112833664160000000NormalM00NULL00:00.00000.662752NULLMATERIAL2020091048E01L000M1063000PLANTubesNAM1063000048E01L0000EANULLORGSORNULLYNYYYNYNULLNULLNULLYES
West Lotions20219/1/20209/30/20209/1/202091150975800211-64300000000NormalM00NULL00:00.00000NULLMATERIAL20200910730031000M1007000PLANTotal Skincare Supply Commit LinesNAM10070007.3E+080EANESLADESLSkincareYYYYNNYNULLNULLNULLYES
West Lotions20219/1/20209/30/20209/1/2020150000000211150007500000000NormalM00NULL00:00.00000NULLMATERIAL20200910730031000M1009000PLANTotal Skincare Supply Commit LinesNAM10090007.3E+080EANESLADESLSkincareYYYYNNYNULLNULLNULLYES
Fragrance20219/1/20209/30/20209/1/20206368000021163683180000000NormalM01NULL00:00.00000.48941NULLMATERIAL20200910943010471M1033000PLANFragranceNAM10330009.43E+080EANULLESLADESLNULLYNNNNNYNULLNULLNULLNO
Tubes20219/1/20209/30/20209/1/202040330415800211-12500000000NormalM00NULL00:00.00000.662752NULLMATERIAL20200910ATX010000M1062000PLANTubesNAM10620000ATX0100000EANULLORGSORNULLYNYYYNYNULLNULLNULLYES
East Lotions20219/1/20209/30/20209/1/2020127000000211127006350000000NormalM00NULL00:00.00000NULLMATERIAL20200910EEP010000M1025000PLANEast LotionsNAM10250000EEP0100000EANULLORGSORNULLYYYYNNYNULLNULLNULLYES
East Lotions20219/1/20209/30/20209/1/2020228700179020021149682480000000NormalM00NULL00:00.00000NULLMATERIAL20200910GGM016000M1028000PLANEast LotionsNAM10280000GGM0160000EANULLORGSORNULLYYYYNNYNULLNULLNULLYES
Creams20219/1/20209/30/20209/1/20201350750102830002113224516120000000NormalM00NULL00:00.00000NULLMATERIAL20200910GGM470475M1003FILPLANCreamsNAM1003FIL0GGM4704750EANULLORGSORNULLYYYYNNYNULLNULLNULLYES
Creams20219/1/20209/30/20209/1/202063490643500211-8600000000NormalM00NULL00:00.00000NULLMATERIAL20200910GGM476476M1003FILPLANCreamsNAM1003FIL0GGM4764760EANULLORGSORNULLYYYYNNYNULLNULLNULLYES
Tubes20219/1/20209/30/20209/1/20204000000002114000020000000000NormalM00NULL00:00.00000.662752NULLMATERIAL20200910GGM550000M1054000PLANTubesNAM10540000GGM5500000EANULLORGSORNULLYNYYYNYNULLNULLNULLYES
Tubes20219/1/20209/30/20209/1/20202800000002112800014000000000NormalM00NULL00:00.00000.662752NULLMATERIAL20200910GW1010000M1063000PLANTubesNAM10630000GW10100000EANULLORGSORNULLYNYYYNYNULLNULLNULLYES
Tubes20219/1/20209/30/20209/1/20206700000021167003350000000NormalM00NULL00:00.00000.662752NULLMATERIAL20200910GWL010000M1062000PLANTubesNAM10620000GWL0100000EANULLORGSORNULLYNYYYNYNULLNULLNULLYES
Tubes20219/1/20209/30/20209/1/20204875000021148752430000000NormalM00NULL00:00.00000.662752NULLMATERIAL20200910HJX010000M1064000PLANTubesNAM10640000HJX0100000EANULLORGSORNULLYNYYYNYNULLNULLNULLYES
Tubes20219/1/20209/30/20209/1/20202700000021127001350000000NormalM00NULL00:00.00000.662752NULLMATERIAL20200910HJX011000M1064000PLANTubesNAM10640000HJX0110000EANULLORGSORNULLYNYYYNYNULLNULLNULLYES

 

mmanisca_0-1600720793751.png

 

@mmanisca 

 

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.

 

darentengmfs_0-1600722149221.png

 

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.

 

darentengmfs_1-1600722225354.png

 

SO the material will appear once per month per resource (could be 2 different resources) would that cause an issue?

@mmanisca 

 

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.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors