cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Dynamic switching COLUMNS using DAX

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Dynamic switching COLUMNS using DAX

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.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Dynamic switching COLUMNS using DAX

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.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Anonymous
Not applicable

Re: 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.PNG

 

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


 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors