Using a slicer to show different measures

[V-lianl-msft]

Scenario:
How to use a slicer to show different measures? This article will show you another way to create dynamic charts based on the external tools (Tabular Editor) updated in July 2020.
I've always done this before about using slicers to display different measures in graph. Create several measures and create a disconnected table to use as slicer. However, this method has the limitation of data format. Since all measures are encapsulated in a switch measure, only one data type is allowed.

Table Used:

Method:

previous method:

For example, we want to dynamically display the rate of product sales in the current month or product sales.

In the previous method, we usually created a disconnected table as a slicer, and then created a switch measure to show the result.

Sum_sales = SUM('Table'[Sales]) 

  

Rate =  

var total_month_sales = 
CALCULATE( 
    SUM('Table'[Sales]), 
    ALLEXCEPT('Table','Table'[Month name],'Table'[Date].[Year])) 

var per_product = [Sum_sales] 

return DIVIDE(per_product,total_month_sales

Create a disconnected table with the measure name and create a switch measure.

Measure = SWITCH(SELECTEDVALUE('Table (2)'[slicer]), 

                         "Rate",[Rate], 

"Sum_sales",[Sum_sales] 
) 

As shown in the picture, this is the limitation of this method. The switch measure can only have one data type. 

Tabular Editor method:

Now we can use the Tabular Editor to achieve this. Below, I will show how to use the Tabular Editor to dynamically switch the measure.

Power Bi announced in July 2020 that public preview of external tool support in Power BI Desktop.

https://powerbi.microsoft.com/en-us/blog/announcing-public-preview-of-external-tools-in-power-bi-des... 

Under external tools, select Tabular Editor. If you don't see these menu options in power bi desktop, you need to load the Tabular Editor first.

Right click the table, select create new, and then click calculation group.

In calculation group, create a new calculated item. Right click calculation items and create a new one.

Using DAX to write the calculation item and set the string format.

The final step is to create measure for the calculation group.

We use this measure to dynamically select calculation items based on slicer.

Save the changes and close the Tabular Editor.

Result:

As shown below, we can create a calculation group through the Tabular Editor to solve the issue of data format of switch measure.

Please check the attached files for details.

Author: Liang Lu 

Reviewer: Kerry and Ula

demo_.pbix