Forum Discussion
Data modelling for Slicer
Hi All,
Need your support. I need to make a customer profile dashboard where there is certain information with respect to customer projects, e.g., type of resources, duration, etc., for a total of 200 different data sets. For reference, KPI-1.1.a and 1.b in the rows are data sets, and customers are in columns, e.g., A, B, C, and D. I have modelled the data and made a matrix-type visual to show the data. But now the ask is to give a slicer for the customer, e.g., data in columns. Can you tell me how I can add customer details (column) data in the slicer so that data for that particular customer is visible in the dashboard?
Raw data format
Thank you so much for your support in advance.
Hii geetika3110
Power BI slicers work only when the thing you want to filter appears as rows in a single column.
Unpivot the customer columns in Power Query
- Go to Power Query >> select all customer columns (A, B, C, D, E…).
- Right-click >> Unpivot Columns.
Now Customer becomes a proper dimension and can be used in a slicer.
5 Replies
Hi geetika3110 , slicers in Power BI cannot directly filter matrix columns.
They filter fields coming from your data model.
Right now, your Customer values (A, B, C, D…) are represented as columns in the table, which makes them not filterable by slicers.
You must unpivot (normalize) the data.
Once customers become a single field in rows, you can:
- Add Customer to a slicer
- Add Customer as Columns in the matrix
- The slicer will filter the matrix correctly
Hii geetika3110
Power BI slicers work only when the thing you want to filter appears as rows in a single column.
Unpivot the customer columns in Power Query
- Go to Power Query >> select all customer columns (A, B, C, D, E…).
- Right-click >> Unpivot Columns.
Now Customer becomes a proper dimension and can be used in a slicer.
Thank you so much for the quick response. Highly Appreciated
Hi geetika3110
You data has to be in the below shape for your model to work. Unpivot columns A-E and create a separate column for your KPI category.
Here's the sample M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY9LCsMwDESvUrwORZJj2TlCyaZ7k0U/9z9DPRM1pRAIQpHeeEa9p/V+u2ia0t+3TT3p9TFaExm1oOhc0Rpa9WCe48+xXRzzHcyVuJCBgZ0ZGA3UKLEyaqXOEEelBgSHpfBBVs2wXDjQYF7hB++Z6vlQSQ7oDQeMGpfwqr5f9M2Zz3LmPWeTOHxo9YjTSjCM+UtAxHF3lSAYUokIFt6OgY+M2wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, A = _t, B = _t, C = _t, D = _t, E = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "KPI Category", each if Text.Contains([Column1], "KPI")then [Column1] else null, type text), #"Filled Down" = Table.FillDown(#"Added Custom",{"KPI Category"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column1], "KPI")), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "KPI"}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"KPI Category", "KPI"}, "Attribute", "Value"), #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"KPI Category", "KPI", "Attribute", "Value"}) in #"Reordered Columns"Thank you so much for the quick response. Highly Appreciated
