Dynamic switching COLUMNS using DAX

v-yulgu-msft I found that unpivot table is more easy to achive this type of results but while unpivot is done we lose original structure of table. If we have used those original columns in any ohter visuals then visual is disappearing. Is there any workaround for this or your code is the only work around to keep original table and at the same time make duplicate Pivot table? Will this increase the memory cosumption if we use two tables?

One more question: How to get only top 5 values for whichever selection i make? If you have solution for this please let me know with your attached eample of PBIX file. Thank you in advance :)

---

v-yulgu-msft wrote:

Hi shaileng,

 

Currently it's not available to dynamically change legend based on slicer selection. Here are two workarounds you can try.

 

1. Create drill down report.

 

2. Convert table structure by creating a new calculated table. This change can also be completed in Query Editor mode via "Unpivot columns". Remember to establish a one to many relationship between this new table and TABLE_SUPPORT.

Please refer to below DAX formula.

Sheet3 =
UNION (
    SELECTCOLUMNS (
        Sheet2,
        "Salary", Sheet2[Salary],
        "Column", Sheet2[COL_A],
        "Selection", "A"
    ),
    SELECTCOLUMNS (
        Sheet2,
        "Salary", Sheet2[Salary],
        "Column", Sheet2[COL_B],
        "Selection", "B"
    )
)

For more detailed steps, please see the uploaded .pbix file.

 

Best regards,

Yuliana Gu

---