Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Transpose/Dynamic Columns based off Slicer

Hi, Im trying to transpose my data and have the columns shown be dynamic based off a related slicer My base data looks like this: I then transpose to this, which is the format I'm looking to...
Need Help
v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

I extract some data from your screenshot as a sample to test.

Please take following steps:

1)Try to copy the table and remove transposed steps in right Applied Steps pane to convert it to the original table to be a slicer table.

2)After Apply&Close, try this calculated table:

New Table =
CROSSJOIN (
    SELECTCOLUMNS ( 'Table', "Type", 'Table'[Contract_Plan_ID] ),
    SELECTCOLUMNS ( 'Table-Copy', "ID", 'Table-Copy'[Contract_Plan_ID] )
)

3)Try this measure:

Measure = 
IF (
    SELECTEDVALUE ( 'Table-Copy'[Contract_Plan_ID] )
        = SELECTEDVALUE ( 'New Table'[ID] ),
    SWITCH (
        SELECTEDVALUE ( 'New Table'[ID] ),
        "H1036_137", MAX ( 'Table'[H1036_137] ),
        "H1036_167", MAX ( 'Table'[H1036_167] ),
        "H1036_168", MAX ( 'Table'[H1036_168] )
    )
)

Note that if you have many [Contract_Plan_ID] values in the original table, you should have to add each ID's formula to SWITCH function.

4)When selecting one value in the slicer, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto