CODEWITHSUNDEEP
powerbidax
are
are

Reputation: 2615

PowerBI Show/Hide Column based on slicer value

power bi

Is it possible to hide/show column in table based on selected value in slicer? or instead of hide/show change value or column depends on selection tnx

Data:

CG  CC  Amount
CG-A    CC-A1   10
CG-A    CC-A2   20
CG-A    CC-A3   30
CG-B    CG-B1   40
CG-B    CG-B2   50
CG-B    CG-B3   60

I have slicer that has 2 values: CG and CC

Slicer
CG
CC

result when CG is selected:

Code    Sum of Amount
CG-A    60
CG-B    150
Grand Total 210

result when CC:

Code    Sum of Amount
CC-A1   10
CC-A2   20
CC-A3   30
CG-B1   40
CG-B2   50
CG-B3   60
Grand Total 210

enter image description here

Upvotes: 1

Views: 10580

Answers (1)

Marco Vos
Marco Vos

Reputation: 2968

One way to achieve this is to unpivot your data.

In the Power Query Editor:

  1. Open a blank query (New Source > Blank Query) and use your data table as the source.
  2. Select the [CG] and [CC] columns and choose Unpivot Columns (Transform tab)
  3. Move [amount] to end (optional) and rename the other two columns to [Slicer] and [Code])
  4. Close and Aply

The result looks like this:

enter image description here

This is the script from the Advanced Editor:

let
    Source = table,
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Amount"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Slicer"}, {"Value", "Code"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Slicer", "Code", "Amount"})
in
    #"Reordered Columns"

In your report put 'tableUnpivoted'[Slicer] in a slicer visual. Then use a matrix and put 'tableUnpivoted'[Code] in Rows and 'tableUnpivoted'[Amount] (sum) in Values. Like so:

enter image description here

Upvotes: 4

Related Questions