Fields parameters in Power BI
Вставка
- Опубліковано 21 лип 2024
- This video analyzes the dynamic parameters feature in Power BI, unveiling some of the internals of its implementation.
IMPORTANT: While this feature is in preview, you must enable it in Power BI Desktop in Options / Global / Preview Features / Field parameters. If you do not enable the feature, also the sample file does not work.
Article and download: sql.bi/751411?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... - Наука та технологія
0:00 introduction to field parameter
2:30 creation of field parameter
5:15 how to edit field parameter(nameof function)
7:09 creation of hierarchy for field parameter
9:09 analyzing queries of field parameter
13:24 be aware of the limitation of field parameter
16:19 how field parameter are stored in the model
19:30 summary and conclusion about field parameter
We were waiting for this for many years, as said it's a much needed feature among others (ex. sets)
However, sad to know that this implementation is only at the client level and not at the model level (TOM). But still welcome this feature in power bi. As always, to get deeper insights, sqlbi is the way to go. Thank you 👍
Hi! Thank you so much for all your videos! Today I found that the order in selection of the fields of a field parameter not always maters. I have a matrix and I am using a field parameter to allow users make their own table. In the Field Parameters I have Columns from my Dimensions Tables (Products, Customers, SalesStructure)
From Products I have selected the ITEM Group, Product Category, Product Subcategory, Brand, Pack Size, Promo etc
From Customers I have the Channel, Chain, Customer
From Sales Structure I have the Manager, SalesPerson
In the Fields Parameters Table I have created a column just for grouping reasons to indicate the table (Products if the column is from Products tables, Customers and SalesStructure) for more tidy look in my slicer.
The problem that I am phasing now (it wasn't at the begging) is when I am selecting with this order
Product Category - Brand - Channel - Pack Size - Promo in my slicer the order in my matrix is different
Product Category - Brand - Pack Size - Channel - Promo.
Why this is happening now? It is a bug?
Thank you, as always, for going a bit deeper than other youtube BI channels. I've been experimenting with these field parameters and time-intelligence calculation groups. So far, the results are somewhat mixed. I did receive a similar "composite key" error message, but overall, the interaction appears to be working. Maybe as a follow-up video, you could explore calculation groups/items working (or not) with field parameters. Thank you!
Indeed. This behind the scenes are truly amazing and insightful. Keep it up Alberto as I learn every time
I always wanted this to easily switch between days, weeks, months instead of drilldown/up. Now it is here. All this time spent making custom tables to get this functionality is no more 😀 Great video as always Alberto!
yep, but how to deal with sorting by value of sales (for exapmle) when you change field from month to week?
I love the "behind the scenes" video. Who would have thought that this is a client-feature, and so is not (yet) working in Excel? Thx, Alberto!
Thank you Alberto! Love SQLBI training. I am trying to "sync" or apply the parameter selection across multiple visuals on multiple report tabs without having to place the "chicklet" on each tab of the report.
Hey. Is there any way to combine the field parameter and a targeting filter? For example: I created a field parameter with month, week and date. Would like to have a slicer to select a specific period. When I filtered month in the field parameter, for example, my slicer would only have month options. If I select week in the field parameter the slicer would only have weeks.
Is it possible to do that?
Amazing detail! Thanks again as usual for this video.
Thank you for the wonderful explanation, however I am getting error as "Missing relationship between these fields"
Great deep dive! Thank you :)
Is there a way to make the name of fields dynamic meaning depending on other measures or varialbles? Thank you
Exactly my thoughts: “How does it work behind the scenes!?” Thanks, Alberto!
Hi Alberto! Great video. How do you work with field parameters, when you're using feature to choose between two measures (not dimensions, as you've shown), and then have both totals, averages, min and max measures in your table? Best, F
Is it possible to refer to the underlying dataset of the selected field parameters?
I have a user defined scatterplot. The user can select the y axis and the x axis. I want to perform the linear regression calculations to get slope and y intercept that is dynamic as the user changes the selections.
Is this possible?
Thank you Thank you Thank you.
Finally Power BI did it, this is a very very needed common feature that all BI thing should have.
I tried to use a measure like Sales = SUM('Sales'[Sales]) in a field parameters, but couldn't use time functions as SAMEPERIODLASTYEAR or PARALLELPERIOD, is there a workaround?
Greaaat explanation, how can i get a dynamic name of my fields from a variable or a measure. This variable or measure is for example selectvalue or concatenatex formula (get the name from a slicer selection) and use it as the name of the field (field parameter). The problem is that when i insert the variable it shows all values of slicer selected even I'm choosing only one in slicer. Having dynamic measure names is a problem for which i can not find a solution
Thank you very much
Thank you Alberto, great as usual!!
But do you agree with me that this is bit confusing to end users because they used to think about slicers as filters not column choosers, can you propuse better visual than slicers to present it to users?
Thanks again
Thank you for the awesome video! Would it be possible to have nested parameters? For example if I need to have one filter pane with $ Sales / Units. And another filter pane with YTD Spend / YoY growth. So that 2 filters will filter one field?
Thank you so much for such detailed video. I am using field parameters in one dataset. I have used that dataset as live connection to build report. I need to import one excel file to this report so I have to convert this live connection to a DirectQuery connection. Because of which my field parameter queries have become table and Not working as it supposed to be. Can you please advise.
Superb Explanation.. 🤟👍
Can we make provision to select a measure and a dimension..we can change these fields to analyze different dimensions against selected measures..
is possible to use functions like allselected so that the values are filtered based on the selections in the field parameters
Amazing feature to analyze products sold!!
I have a repot with pareto chart in power bi,clicking individual item in paret chart should drill down to next level(can we get drill down level in pareto analysis form)can this possible in power bi?
Hen I use field parameters for my metrics and dimensions, the ordering of the bars in a bar chart is always according to the metric, not the dimension. I have 1 case in which this is not the case, a column that is sorted by another column. Can anyone recommend a way to make sure the sort ordering of bars is cone on the dimension and not the value of the metric?
Hi Alberto,
We configured a stacked bar/column chart with dynamic x axis and legend with the help field parameters.
Though in power bi service I am unable to export the data properly.only summarized export is available(with out any dimension).
Could you please help.
Hi Alberto, great video! This is a scenario that Im facing.
I created the fields parameters in my report and then publish it.
Here is the issue, if I connect to that dataset as live mode everything works fine. But If I connect to it by direct query mode it fails.
Is this a limitation?
Excellent video Alberto 🙂👍
Hi Alberto! great explanation as always, Im curious about a hierarchy that goes in the opposite direction, can I pull the values in each column into the filed parameters table?
Can field parameters be used in built-in top N for line chart?
My line charts has many series about 200 and it caused the distorted scale (Y axis scale is not in line with the series' actual value).
So, I needed to set top N to get correct scale, but top N with field parameters didn't control to N
Excellent, Alberto!!! 🥰🥰🥰🥰. This new feature is amazing.
I'm trying to add a tooltip with a percentage compared to "all of the field parameter". i.e. If I have the field parameter set to product, I want DIVIDE(Metric, Metric for all "Product"). Is there a way to do that? I could do a CASE for each one supported, but I can't make anything happen directly with the field parameter measure itself.
Can I add rows of Column in Field Parameters?
I noticed some strange behavior. When I Copy-Paste the slicer that has Field Parameter and then, turn it into a matrix, it still shows only the "fields" options. But when I remove the Parameter field from the matrix and then simple return the Parameter to rows, it suddenly shows all the values. Why does it behave like this?
Hi , I want to make one column fixed - Always selected and want enable parameter selection for other fields. Can we do it?
Always awesome. Thank you.
Great. Which is what I'm looking for
Odd question... I have table visual with "Actuals" and a Field Parameter for two different types of budgets. Next in the visual I really want variance (Actual - Budget) and Variance % as the next two
columns in the table. However I want the two variance columns to calculate based on the budget field parameter selected. Is there a clean way to do this. I don't want to show want budget type, but accidentally show a variance on another budget type. I even tried using the field from the field parameter in the variance calculations but that did not work.
any clean simple way to do this?
Hello, thanks for your video. It is possible to get the value of measures of field parameter table for conditional formating?
Probably no.
Great video of a great feature. But isn't it a shame that we still have to stick to just basic formatting capable slicer visual?
Hello, thanks for great tutorial! I want to ask if it is possible to put IF statement to field parameter formula?
You can change the calculated table generating the field parameter table, but being a calculated table it is evaluated when you refresh the model and it ignores the filters applied to a report when it is evaluated.
Hi Alberto. I have a matrix that shows my profit and loss. the values have 3 Columns Actual, Prior Month and Variance. When I change the month using a slicer (i.e. October 2023). The Actual column shows my Oct 20V23 Values and the Prior Month Measure returns the September 2023 Values and the Variance computes correctly. Is there a way to have the title for the Actual and Prior Month columns indicate October 2023 and September 2023, respectively? The Variance column would remain as Variance.
Thanks Alberto....
Thank you so much for the knowledge! I'm curious to know what is the difference between field parameters and calculation groups? It feels like they can be used to achieve the same results but I have no working experience on this. Your thoughts please?
Calculation groups are a modeling feature that work regardless of the client (they are supported on both Excel and Power BI, and in any other MDX or DAX client).
Field parameters are a Power BI feature that is not supported by other clients because it is not supported directly by the Tabular model.
Can we use value from 1 field parameter within another one? I would like to create a dynamic table which will dynamically change values and column names using 1 slicer
No.
Thank you for the deep explanation. Do you think whether the new field parameter feature can replace Calculation Group when switching multiple measures (Current Month/Previous Month/Same Month Previous Year/YTD and Order Qty/Revenue/Margin)? Thank you.
Different techniques. Calculation groups works also in Excel, Fields parameters do not.
Hi Alberto, will measure based field parameter work along with a Calculation group?
Yes they should.
Great video, congrats!
Quick question: let's supose I have 2 field parameters with 2 options each (so total of 4 measures, which are the result of the combinations of those 4 options).
The requirements involve having those 2 field parameters separeted, so I need to display the result from the combined selections. Which way would you recommend to implement?
Just FYI, I've already solved it by creating one field parameter with all 4 measures, and additional columns to use as filters to identify the groups. So I've added two separate filter visuals, one for each column (both single value only) and It's fine... just want to know if you have any other suggestion...
Hi Thanks for Video. Also is it possible to share the Pbix file so play around.
Use the link to the article in the video description.
thank you for the video
but I'm really curious what kind of keyboard you use ...
All the details here: www.sqlbi.com/blog/alberto/2021/02/14/on-my-recording-gear/
To calculate the percentages in a table or matrix, SWITCH and ISINSCOPE were used, but now the field parameters (which users can set the number they want and in the order they want) how can it be calculated so that the result is correct when the user adds, deletes and changes the order of the field parameters?
Very good point - you are in trouble because there is no way you can write DAX code that can dynamically adjust to combinations defined by Fields Parameters.
Is the "Extended Properties" setting only available in Tabular Editor 3? I don't see this setting in Tabular Editor 2 😞
It is available also in Tabular Editor 2. Make sure you enable the access to unsupported properties.
@@SQLBI Thank you a ton! This feature is so cool!
This helped me improve a dynamic combo chart that I had created. My earlier version has features like:
1. Different column bars by the type of measure - example: Total Budget in 1 group and "stacked components" of Actuals in another
2. Some measures to be in Column stacks like above and some as trendlines (using them as lines and not columns)
My earlier version showed all the legends even if the measure wasn't on the chart.
Using this dynamic fields feature and Extended Properties of ParameterMetadata = {"version":3,"kind":2}, I am able to now show only the legends of those measures that are filtered by a group of measures.
Sorry, where I can have field parameters? Become I have only number parameters and can’t change it to field parameters. Maybe someone know about it?
While in preview, you should enable Fields parameters in preview features (Power BI options dialog box)
Hello Alberto,
I have an issue when creating a hierarchy as you did for different tables Table name --> Column names for three different tables. After selecting three different fields from the slicer, it is mixing the order of my selections.
Assume my tables and columns are :
1. Countries
a. Continent name
b. Country name
2. Customers
a. Costumer group
b. Customer team name
c. Customer name
3. Products
a. Category
b. Manufacturer
c. Brand
d. Sub-brand
If I make a selection in the following order Country name --> Customer name --> Category, what I expect in a matrix visual the row hierarchy will be as in my selection order but what I get is Customer name --> Country name --> Category. I was also faced with different variations of this mix order.
Do you have any idea about it? Why does it not show hierarchy in my selection order?
I have the same problem too. Hopefully its a bug that will be fixed
The problem persists in the june update :-(
Thanks for the great video Alberto. Is it possible to make a hierarchical field parameter like a silcer? For instance a dropdown list where under 'product category' i have 'product subcategory' and under this i have 'product name'? (with actual dimension columns not custom categories as you showed)
Probably yes, but you should customize the Parameter table making sure that the metadata is valid without relying on the user interface in Power BI that creates the Parameter table for you.
Also, they should add the possibility in the service for the users to just drag-and-drop fields into visuals, without editing the report.
Can't wait for Bernat's script to create these from TE :-)
I just downloaded the most recent version of Power BI from the Microsoft website and I still only have the "What if" parameter option. There is no drop down option to select "Field Parameter". My version of Power BI is: 2.106.582.0 64-bit (June 2022). Do you have any idea what I am doing wrong? Thanks so much.
You should enable the feature in the Power BI Options panel, it's a feature in preview.
@@SQLBI Awesome thank you.
What about a non-heirarchical use case? What if I wanted to swap between a top 25 list of products and a top 25 list of stores? Currently, visuals only allow for one TopN filter, and adding the newly created parameter field seems to have no effect on the visual.
Obviously, I could create a lot of special use case measures, but I was hoping for ways to consolidate and reduce complexity in my models for end users, not adding more.
I would need the equivalent of "named set" available in Excel, but there is not such a feature in Power BI yet.
@@SQLBI Yes, "sets" are available in MDX and also other bi tools like Tableau, which power bi users still awaiting for.. hopefully it comes into TOM ..
Thanks
can we do the same without field parameters?
if yes, can you share the link or article on it
The alternative are calculation groups www.sqlbi.com/calculation-groups/ or disconnected parameter table www.daxpatterns.com/parameter-table/
Thanks a lot@@SQLBI
the field parameter feature doesnt seem to be available in Jan 2023 Power Bi Report server.....
Probably you have to wait another release...
@@SQLBI too bad. i would love this feature for my work project. did you make a video of this function before field parameters were introduced?
I like this guy
I have a field parameter that I have added in table visual along with cumulative total measure. Because I want 1st column to change based on field parameter selection in slicer and also cumulative total measure to compute cumulative total for selected field parameter. It works fine when user selects Subject Code Description but does not compute cumulative total when user selects Month Name. All the fields are coming from one single table. Following is the code. Can you please help me understand what is wrong in my code.
Common Cumulative Total =
VAR totalCaseCount =
SUM ( Data_Source[Merge_Complaints_Count] )
VAR L4Level =
SELECTEDVALUE(L4[L4 Order])
RETURN
IF(
L4Level=0,
CALCULATE (
SUM ( Data_Source[Merge_Complaints_Count] ),
FILTER (
ALLSELECTED ( Data_Source[Month Name] ),
CALCULATE ( SUM ( Data_Source[Merge_Complaints_Count] ) >= totalCaseCount )
)
)
,
IF(L4Level=1,
CALCULATE (
SUM ( Data_Source[Merge_Complaints_Count] ),
FILTER (
ALLSELECTED ( Data_Source[Subject Code Desc] ),
CALCULATE ( SUM ( Data_Source[Merge_Complaints_Count] ) >= totalCaseCount )
)
)
)
)
I figured why it was not working for Month. For Month Name, I had mentioned sort by Month Number. And in calculation above I am mentioning only Month Name. After I added Month Number along with Month Name, it started calculating cumulative total correctly.
I spent several minutes gazing at the code of the Field Parameter's calculated table not understanding how the Power BI knows what to calculate...
Why won't this work if I place a field parameter in the Filters Pane?
It is only supported as slicer/rows/columns
Perfect
Cant wait untill my company upgrades to this version of pbi
This is an exciting new feature, I must say.
One thing I tried is to use these to control a slicer instead of a matrix (or other visual).
So instead of having 15 slicers on my screen, I just have one slicer with 15 dimensions in it. I was wanting to then choose one of them. Then my second slicer on same page becomes that dimension. So it's a slicer-slicer.
In cases where you may want to slice by a lot of dimensions, my theory is that it can be done with just two slicers.
In slicer A you pick the dimension. Then slicer B becomes a slicer for that dimension.
Any thoughts on that?
It doesn't work, the slicer can get the list of values in the fields parameters that drives the choice on matrix/chart visuals, but not for another slicer.
@@SQLBI That's what I was afraid of, thanks. :)
I have What If not New Parametr. Why?
You must enable the feature in the preview features of Power BI
@@SQLBI I do it , so happy , thank you 🙏 , for teaching
Great feature. If only our data model wasn’t sat in AAS and our reports used live connect….
If you transform the live connection model into a composite model, you can use this feature.
Hi @@SQLBI, one question related to this option, at performance level, Is there any specific disadvantage when changing the connection from live connection to a direct query for AAS? Just wanted to know if it would be worth changing the connection to AAS to only get this feature (Field Parameters) benefit 😅
Holly shit how do you learn this from ???
only took MS 6 years to implement this.