Transpose/Dynamic Columns based off Slicer

[Anonymous]

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 display on the report page:

The challenge here is all of the column headers need to be contigent on the related slicer, I.e. for slicer selection 1, only Contract_Plan_ID = "H1036_137" should display. Ive tried both a matrix and a table but cant seem to have this dynamically adjust based off the slicer and roll up to the format Im looking to achieve. Any guidance would be most appreciated!

[v-gizhi-msft]

Hi,

Did my last reply solve your issue?

If my answer has solved your issue, please mark it as a solution for others to see.
Thanks!

Best Regards,

Giotto

[v-gizhi-msft]

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

[amitchandak]

Refer, if this can help

https://yodalearning.com/tutorials/power-query-helps-transposing-data/

https://radacad.com/pivot-and-unpivot-with-power-bi

[Anonymous]

Thanks for the suggestion! Unfortunatley I still cant seem to limit what columns are displaying after transposing/pivoting a column. I can get the data to pivot correctly, but limiting what columns display after the pivot is still a problem.

i.e. Slicer selction 1 has records for contract plan H1036_137 and H1036_167. When i pivot the data, I have columns for these two contracts plans (which is what I want to include) and all the other contract plans (which is what I do not want to include). Is there a way to limit the colums displayed to solely whats correlated to the slicer?

[amitchandak]

@ImkeF , can you help on this.

[ImkeF]

Hi @Anonymous#w ,

having difficulties to understand what's requested here. 

First impression was that you shouldn't unpivot your data.

You're starting from a table with a defined structure and fixed number of columns and transform it to a table with an unknown number of columns (and therefore column names). Why do you do that?

[Anonymous]

Hi,

Thanks for the feedback! The way the data is originally stored with the fixed structure does not meet the requirement as the goal is to replace a manual process with minimal layout change. All of the columns in the base data are attributes to the Contract Plan ID. Since a Contract Plan ID has roughly 100 attributes, it reads easier if the attributes are listed out in rows instead of columns as there will be at most 10 Contract Plan IDs

Its almost as though I need to apply the slicer prior to transposing the data to get the correct Contract Plan IDs to show however I havent found a way to do this as the slicer is dynamic as well

[ImkeF]

For me a slicer is an element on the report canvas. But as we're in Power Query land here: What do you mean with that here exactly?

A filter on a column?