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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

v-yiruan-msft

Field Parameters in Power BI

Scenario:

The May 2022 version of Power BI Desktop includes a very interesting and useful feature – Field Parameters. To better help you understand this new function, I would like to generally introduce it and share with you about some common application scenarios.

 

Introduction

Field: a table column or measure.

Parameters: is a variable.

Field parameters: Field parameters allow users to dynamically change the measures or dimensions being analyzed within a report. We can use the parameter to control the measures or dimensions used in a visual.  

 

The parameter table has three columns, the first is the parameter name, which is the name displayed in the slicer, the second is the field source (measure and column), and the third is the parameter order.

yingyinr_0-1655886103938.png

 

PS: Some people may ask, is it possible to add a field parameter to be blank? Not currently. If we add a blank field, an error occurs when rendering the report.

yingyinr_1-1655886285333.png

yingyinr_2-1655886285335.png

 

Steps:

Original Table:

a) Table

Date

sales

StorelD

YMonth

Product

quantity

Salemanager

3/1/2022

56

2

2022-3

Product6

5

Amy

4/3/2022

40

1

2022-4

Product6

10

Amy

1/2/2022

49

3

2022-1

Product3

7

Amy

1/22/2022

53

2

2022-1

Product4

3

Amy

2/11/2022

52

1

2022-2

Product3

5

Helen

3/3/2022

33

1

2022-3

Product4

9

Helen

3/23/2022

22

3

2022-3

Product5

8

Helen

4/12/2022

27

2

2022-4

Product2

6

Paul

3/1/2022

35

1

2022-3

Product3

12

Paul

4/3/2022

33

3

2022-4

Product5

2

Paul

1/2/2022

57

3

2022-1

Product6

5

Paul

7/1/2022

57

1

2022-7

Product1

8

Paul

7/21/2022

41

3

2022-7

Product3

7

Paul

3/3/2022

43

3

2022-3

Product4

9

Wendy

3/23/2022

42

1

2022-3

Product2

3

Wendy

4/12/2022

47

3

2022-4

Product2

4

Wendy

3/1/2022

25

1

2022-3

Product6

6

Wendy

4/3/2022

37

3

2022-4

Product5

5

Wendy

1/2/2022

53

3

2022-1

Product2

3

Wendy

 

b) City

StorelD

city

1

A

2

B

3

C

 

Scenario 1: Dynamic Coordinate Axis

Click New Parameter > Field, in the pop-up window, all fields in the model can be selected on the right:

When creating a clustered column chart, the x-axis is generally a fixed field. If we want the x-axis to be dynamically changed according to different selections of the slicer, we can use buttons + bookmarks to achieve this, but it is relatively complicated; So we can use the field parameter to easily change x-axis dynamically through the slicer selection.

yingyinr_3-1655886418122.png

 

Scenario 2: Dynamic Values

When using the measure as the value or Y-axis in the chart, different values can be displayed dynamically, and different data formats (integer or percentage) can be displayed. Create a new field parameter and add sales measure and quantity measure. Then as the Y-axis of the chart. The displayed results show:

yingyinr_4-1655886418142.png

 

What’s more: the value or Y-axis here can only be the measure, when using column fields. The graph will appear blank.

yingyinr_5-1655886418143.png

Scenario 3: Full Dynamic chart

The first two scenarios are for the dynamic display of the X-Axis and values, so the combination of the chart will naturally be full dynamic display. Create three different field parameters: X-axis, Y-axis or value, Legend. And add them to their respective locations. The display is as follows:

yingyinr_6-1655886502294.png

 

 

Summarize
Field Parameters VS SWITCH TRUE

If you want to dynamically switch values, we can also use the SWITCH TRUE method. However, the value returned by the SWITCH TRUE method can only be in one format, and Field Parameters can dynamically display values in different formats.

 

In short, Field parameters enable dynamic dimensions and calculations, Including: dynamic axis, dynamic legend and dynamic value.

Field parameters are useful because they allow users to seamlessly change the columns used in visual objects. Field parameters can provide a very customizable user experience.

 

 

Author: Yalan Wu

Reviewer: Ula Huang, Kerry Wang

Comments

@v-yiruan-msft This is a great feature. Have you found a way to also incorprate slicers? So a user can select what fields they want included but also be able to filter their selection?

Would there be a way to look at a curren month vs the trend? If for example you had 2 versions of the table (1 for current month) and one for cumulative, could you introduce that so that the measure would switch tables?

 

Thanks

 

@v-yiruan-msft: similarly to @TeaPea888's requst it would be extremely helpful in a lot of scenarios to allow switchting between "Show selected field" and "Show value of selected field" on slicers as well. That would allow using one slicer to select the (parameter) filter field and a second slicer to dynamically filter on the values of the aforementioned parameter selection.

Best blank value today

What I want to do is to show orders as number of orders and the value of orders. As these two won't scale to same Y range, I have used line chart and created two selection lists:

TimoRiikonen_0-1665056659966.png

Then assigned these as

TimoRiikonen_1-1665056702467.png

This works well until I want to show only either numbers or values. Then I am not able to choose nothing from the selection list. So I had to add "None" option, which uses one graph that is always zero. After that I make its line width as zero points, so it disappears from the graph. But the problem comes with the Y axis. If you have always zero value, it shows from -1 to 1 or such, which looks bad. So I replace zero line with value 1 instead. Or I hard-code min and max values, which is worse.


    Quantity graph = {

        ("None", NAMEOF('_Measures'[Zero line]), 0),
        ("New orders", NAMEOF('Sales Invoice Header - by order date'[New orders]), 1),
        ("Shipped orders", NAMEOF('Sales Invoice Header'[Shipped orders]), 2),
        ("Open orders", NAMEOF('_Measures'[Open orders]), 4),
        ("Late orders", NAMEOF('Sales Invoice Header'[Late delivery]), 6)
    }


    Zero line =
1
 
Result: Line is not shown and Y axis content is a bit bad, but still (barely) acceptable:
TimoRiikonen_2-1665057532993.png

 

But actually I managed to temporarily delete my zero line for a moment and then result was perfect: The second Y axes disappeared altogether. So if you could send a recommendation to the DEV team to create null option, it should be pretty easy to do.

hi

 

The chart is showing nothing, I followed the steps. Anything I may be missing? Please advice. 

 

julicole_1-1665594216216.png