Dynamic switching COLUMNS using DAX

[shaileng]

Hi there,

Quick DAX question :

- I have a table TABLE_DATA with 3 cols COL_A,COL_B,COL_C.

- I have another table named TABLE_SUPPORT which holds 3 rows like "A","B" and "C

- I have a report with 2 visuals i.e.,

1. A drop down slicer based on TABLE_SUPPORT to select a value from one of 3 options
2.  A pie chart which is expected to draw the pie based on the selection in the slicer

So if Option "A" is selected then COL_A should be used to populate the pie. If Option "B" is selected then COL_B should be used to populate the pie and similarly Option "C" is selected then pie should be populated using COL_C.

I could achive this if COL_A and COL_B are numeric values (given in page 1) however in reality my COL_A and COL_B are text fields which are to be used on pie chart showing them as legends. Now since pie chart will require column (and not measure) in legend field. I was trying to convert the measure to column however this is not working..

Any help will be really appreciated as this is super urgent for me.

Sample File location

Thanks
Shailen

[v-yulgu-msft · Accepted solution]

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

[Anonymous]

@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

---