How to create slicer from column name

Question

I have a sample table as shown below:

Table

Quarter Month   Status  Debit   Digital Internal    IVT Risk
Q1  Jan'22  Plan    57  92  33  9   60
        Actuals 46  92  33  9   60
    Feb'22  Plan    58  72  36  8   65
        Actuals 50  72  37  8   65
    Mar'22  Plan    71  28  30  22  2
        Actuals 44  28  29  22  2

Here is the report:

enter image description here

I am creating a hierarchical bar chart out of it

I have to make a slicer/dropdown filter based on some column name as shown below:

Columns

How to create slicer without affecting hierarchical bar chart.

I have created a measure like:

Parameter = {
    ("Digital", NAMEOF('Power BI Fact Table'[Digital]), 0),
    ("Debit", NAMEOF('Power BI Fact Table'[Debit]), 1),
    ("Internal", NAMEOF('Power BI Fact Table'[Internal]), 2),
    ("IVT", NAMEOF('Power BI Fact Table'[IVT]), 3),
    ("Risk", NAMEOF('Power BI Fact Table'[Risk]), 4)
}

But it is not changing bar chart after selection.

Please provide sample data in text format. And where's your *hierarchical bar chart*? — Peter, Sep 17 '22 at 13:51
Read about Field Parameters: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters — David Browne - Microsoft, Sep 17 '22 at 14:05
@Peter I have edited question with sample data in text form and hierarchical bar chart — , Sep 18 '22 at 04:26
@DavidBrowne-Microsoft I tried creating parameter but it is not changing bar chart, Updated my question — , Sep 18 '22 at 05:01

score 1 · Accepted Answer · answered Sep 18 '22 at 08:12

1

The problem here is the half-baked data model and especially a pivot table will bring you nowhere in Power BI. So the work starts in Power Query, where you have to unpivot your Attribute columns Debit, Digital, Internal, IVT and Risk to get a stacked table like shown below

Table.UnpivotOtherColumns(#"Changed Type", {"Quarter", "Month", "Status"}, "Attribute", "Value")

From here everything becomes plain vanilla and you can simply pull in the new Attribute column and use it as a slicer:

answered Sep 18 '22 at 08:12

Peter

10,959
2
30
47

It is a bad idea to post a solution which is almost the same as others. Why can somebody do that? – Ozan Sen Sep 18 '22 at 08:24
I've been in the conversation to this post since yesterday and the questioner pointed me again to the issue just 3 hours ago. So I put the solution together without being aware of your post. Btw. I posted a similar [unpivot first!](https://stackoverflow.com/questions/73732410/dax-calculate-the-yoy-score-change/73733071#73733071) solution 2 days ago, since it's a very common mistake to work with wide tables in Power BI. – Peter Sep 18 '22 at 08:33
Then next time, before you post something, check that other people has posted something similar which I always do! – Ozan Sen Sep 18 '22 at 08:40
@Peter can you please tell how to show plan first then actual value in x axis in above chart – Sep 19 '22 at 11:36

Ozan Sen · Answer 2 · 2022-09-18T12:39:11.583

I solved it without field parameters. You need to use unpivot columns first in the query editor:

Your Initial Data set :

should be converted Into this using unpivot other columns:

You can access unpivot function like you can see below:

Full M-Code in Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRU0lEyMtL1SswDMgJywJSpOZCwNAISxsYgFhCbGSjF6qArd0wuKU3MKQayTMwI6XBLTUKywAJImIOVgzSCeGamWJQjLDA1gOswx67DN7EIYYE5WBCkytgALA0isChH8oEJTIeRJZKOWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, Month = _t, Status = _t, De = _t, Di = _t, In = _t, Iv = _t, Ri = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Quarter", "Month", "Status"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Metric"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}})
in
    #"Changed Type"

then create a simple measure for value field to be used as slice/dice:

Total = SUM('Power BI Fact Table'[Value])

Then start shaping your bar/column chart:

To build slicer, just put metric field Into it simply:

If we test it:

How to create slicer from column name

2 Answers2

Linked