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 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!

 

Need Help

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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?

  • 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

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    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