Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

V-lianl-msft

Using a slicer to show different measures

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:

V-lianl-msft_0-1615883192031.png

 

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.

Liang_blog2.png

 

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. 

V-lianl-msft_2-1615883306791.png

Liang_blog3-2.png

 

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.

V-lianl-msft_3-1615883354632.png

Liang_blog5.png

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

V-lianl-msft_4-1615883371885.png

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

V-lianl-msft_5-1615883385025.png

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

Liang_blog8.pngLiang_blog9.png

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

Liang_blog10.png

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.

Liang_blog11.pngLiang_blog12.png

 

Please check the attached files for details.

 

 

Author: Liang Lu 

Reviewer: Kerry and Ula