Hi Wen,

There are a few ways to tackle this, some less complex than others:

1. Create two Dashboards, one for English and one for Chinese and set up Hyperlink Buttons for the user to navigate to the dashboard of their choice. 

2. Put your English & Chinese data in the same table (one under the other so the columns line up, not side by side) and add a column that identifies the language. You'll use this column as your Slicer field. This is easy, but if no language is selected then the report will double count the data. You can always set up a control that alerts the user that they haven't selected a language and thus hide the data until they do. Let me know if you go with this option and I can give you extra guidance on how to set up the control.

3. Use Power Pivot and disconnected tables to switch the view from one table to the other. This post explains how to do it in Power Pivot in Power BI, which is pretty much the same as Power Pivot in Excel. This is by far the most complicated. 

I hope that gives you some ideas.

Mynda

Thank you Mynda, 

Thanks for your reply, I will go for the second one, can you show me how to set up a control? 

BR,

Wen 

Sure. Create a dummy PivotTable that contains just the language field in the row labels. When you insert your Slicer you need to connect it to this PivotTable, as well as the PivotTables supplying your Dashboard. When a user selects a language your dummy language PivotTable will show one of the two languages only. If no language is selected in the Slicer it will show two languages. You can apply a formula to check if there are two languages in the PivotTable.

Let's say language 1 is displayed in cell C5 of the PivotTable and language 2 is in cell C6. All you need to do is check if C6 is empty, if it's not then you can return a warning message. e.g.:

=IF(NOT(ISBLANK(C6)),"WARNING, NO LANGUAGE SELECTED","")

You can change the warning message to anything you like.

You can also take it a step further and perform a logical test that returns TRUE or FALSE depending on whether the second language field is, or is not empty . e.g.:

=C6=""

The above formula will return TRUE if C6 is empty and FALSE if it contains a value or text.

You then multiply all of the tables feeding your dashboard by the outcome of this logical test. When you multiply TRUE or FALSE they return their numeric equivalent of 1 and 0 and if you multiply anything by zero, the result is zero, thus hiding all of the data in your dashboard. That said, if you're using PivotTables it would be a considerable amount more work to implement this last step. I would probably just use the warning message, which you can make big and red!

Mynda