Forum Discussion
Create a filter with multiple choice
Hi I new on Power BI and I don't know if it possible, but I would like to create a filter which can filter a table if it contain the specific value. To explain, I have a column which describe an object with some type it can have differentes types. Here some exemple :
1 - ELEVATIQUE ET APE / IOT - GTB - GTC / MOBILIER / ABRIS FILANTS - MARQUISES - AUVENTS / PASSERELLES - PASO - PONTS ROUTES / CVC
2 - CVC / IOT - GTB - GTC / INFO V - SONOS
3 - QUAIS / ABRIS FILANTS - MARQUISES - AUVENTS / PASSERELLES - PASO - PONTS ROUTES
4 - ENERGIE / IOT - GTB - GTC
....
and i would like to filter my table with the type, for exemple i would like to have only the objet which are "CVC", it show only the 1 and 2 on my exemple. Or if i would like thouth with "IOT - GTB - GTC" it show the 1, 2 and 4.
Is that possible to do it ? and how can I do it?
Hi JTCHA9
To create the selector I will do the following:
In Power Query, duplicate the original table twice and keep only the UT and Category (we will create a relationship later on).
First table (original) will be named MyTable
Second table will be named Relation Table
let Origen = Excel.Workbook(File.Contents("C:\example.xlsx"), null, true), Hoja1_Sheet = Origen{[Item="Hoja3",Kind="Sheet"]}[Data], #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]), #"Otras columnas quitadas" = Table.SelectColumns(#"Encabezados promovidos",{"UT", "Category"}), #"Tipo cambiado" = Table.TransformColumnTypes(#"Otras columnas quitadas",{{"UT", type text}, {"Category", type text}}), #"Dividir columna por delimitador" = Table.SplitColumn(#"Tipo cambiado", "Category", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Category.1", "Category.2", "Category.3", "Category.4", "Category.5", "Category.6"}), #"Tipo cambiado1" = Table.TransformColumnTypes(#"Dividir columna por delimitador",{{"Category.1", type text}, {"Category.2", type text}, {"Category.3", type text}, {"Category.4", type text}, {"Category.5", type text}, {"Category.6", type text}}), #"Columna de anulación de dinamización" = Table.UnpivotOtherColumns(#"Tipo cambiado1", {"UT"}, "Atributo", "Valor"), #"Columnas quitadas" = Table.RemoveColumns(#"Columna de anulación de dinamización",{"Atributo"}), #"Texto recortado" = Table.TransformColumns(#"Columnas quitadas",{{"Valor", Text.Trim, type text}}) in #"Texto recortado"Third table will be named Categories:
let Origen = Excel.Workbook(File.Contents("C:\example.xlsx"), null, true), Hoja1_Sheet = Origen{[Item="Hoja3",Kind="Sheet"]}[Data], #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]), #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"UT", type text}, {"Category", type text}}), #"Otras columnas quitadas" = Table.SelectColumns(#"Tipo cambiado",{"UT", "Category"}), #"Dividir columna por delimitador" = Table.SplitColumn(#"Otras columnas quitadas", "Category", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Category.1", "Category.2", "Category.3", "Category.4", "Category.5", "Category.6"}), #"Tipo cambiado1" = Table.TransformColumnTypes(#"Dividir columna por delimitador",{{"Category.1", type text}, {"Category.2", type text}, {"Category.3", type text}, {"Category.4", type text}, {"Category.5", type text}, {"Category.6", type text}}), #"Columna de anulación de dinamización" = Table.UnpivotOtherColumns(#"Tipo cambiado1", {"UT"}, "Atributo", "Valor"), #"Columnas quitadas" = Table.RemoveColumns(#"Columna de anulación de dinamización",{"Atributo", "UT"}), #"Texto recortado" = Table.TransformColumns(#"Columnas quitadas",{{"Valor", Text.Trim, type text}}), #"Duplicados quitados" = Table.Distinct(#"Texto recortado") in #"Duplicados quitados"Once I have the tables, relate them:
And finally, you create your slicer:
Be aware that UT shown in the matrix should be the one of Relation Table. Otherwise, slicer won't work
1 Reply
Hi JTCHA9
To create the selector I will do the following:
In Power Query, duplicate the original table twice and keep only the UT and Category (we will create a relationship later on).
First table (original) will be named MyTable
Second table will be named Relation Table
let Origen = Excel.Workbook(File.Contents("C:\example.xlsx"), null, true), Hoja1_Sheet = Origen{[Item="Hoja3",Kind="Sheet"]}[Data], #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]), #"Otras columnas quitadas" = Table.SelectColumns(#"Encabezados promovidos",{"UT", "Category"}), #"Tipo cambiado" = Table.TransformColumnTypes(#"Otras columnas quitadas",{{"UT", type text}, {"Category", type text}}), #"Dividir columna por delimitador" = Table.SplitColumn(#"Tipo cambiado", "Category", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Category.1", "Category.2", "Category.3", "Category.4", "Category.5", "Category.6"}), #"Tipo cambiado1" = Table.TransformColumnTypes(#"Dividir columna por delimitador",{{"Category.1", type text}, {"Category.2", type text}, {"Category.3", type text}, {"Category.4", type text}, {"Category.5", type text}, {"Category.6", type text}}), #"Columna de anulación de dinamización" = Table.UnpivotOtherColumns(#"Tipo cambiado1", {"UT"}, "Atributo", "Valor"), #"Columnas quitadas" = Table.RemoveColumns(#"Columna de anulación de dinamización",{"Atributo"}), #"Texto recortado" = Table.TransformColumns(#"Columnas quitadas",{{"Valor", Text.Trim, type text}}) in #"Texto recortado"Third table will be named Categories:
let Origen = Excel.Workbook(File.Contents("C:\example.xlsx"), null, true), Hoja1_Sheet = Origen{[Item="Hoja3",Kind="Sheet"]}[Data], #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]), #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"UT", type text}, {"Category", type text}}), #"Otras columnas quitadas" = Table.SelectColumns(#"Tipo cambiado",{"UT", "Category"}), #"Dividir columna por delimitador" = Table.SplitColumn(#"Otras columnas quitadas", "Category", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Category.1", "Category.2", "Category.3", "Category.4", "Category.5", "Category.6"}), #"Tipo cambiado1" = Table.TransformColumnTypes(#"Dividir columna por delimitador",{{"Category.1", type text}, {"Category.2", type text}, {"Category.3", type text}, {"Category.4", type text}, {"Category.5", type text}, {"Category.6", type text}}), #"Columna de anulación de dinamización" = Table.UnpivotOtherColumns(#"Tipo cambiado1", {"UT"}, "Atributo", "Valor"), #"Columnas quitadas" = Table.RemoveColumns(#"Columna de anulación de dinamización",{"Atributo", "UT"}), #"Texto recortado" = Table.TransformColumns(#"Columnas quitadas",{{"Valor", Text.Trim, type text}}), #"Duplicados quitados" = Table.Distinct(#"Texto recortado") in #"Duplicados quitados"Once I have the tables, relate them:
And finally, you create your slicer:
Be aware that UT shown in the matrix should be the one of Relation Table. Otherwise, slicer won't work