cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Using parameter/ filter values in DAX calculation

Hello!

I have a database of products which has these columns: date, state, product, competitor_name, competitor_price, my_price and stock. Below I give you an example of that table:

date           | state         | product       | competitor_name | competitor_price | my_price | stock

2024-06-17 | São Paulo | diaper         | Competitor A        | \$10.00                 | \$12.00     | 1

2024-06-17 | Minas       | diaper         | Competitor B        | \$13.00                 | \$12.00     | 0

2024-06-17 | Rio           | diaper         | Competitor C        | \$10.00                 | \$12.00     | 1

2024-06-17 | São Paulo | sunscreen   | Competitor A        | \$40.00                 | \$36.00     | 1

2024-06-17 | Minas       | sunscreen   | Competitor B        | \$32.00                 | \$36.00     | 1

2024-06-17 | Ceará        | sunscreen   | Competitor D        | \$38.00                 | \$36.00     | 0

The column `product` is a key for a specific product; `stock` is the stock of this product in the competitor's website (0 is out of stock, 1 is in stock), and `competitor_name` is the name of the specific competitor. What I do with this database is to compare my price with the competitor's price, using the stock column as a filter. Notice that the column `my_price` has the same value for the same product. My goal is to create a dynamic calculation of the difference between these two prices. I will simply call it `diff` and define it as such:

diff = my_price / competitor_price - 1

I could just create a new column with that calculation, and in fact that's what I've been doing. But I need to take the stock into account: it's a binary column, so I could make something like this:

diff = CALCULATE ( my_price / competitor_price - 1, stock = stock_filter )

where "stock_filter" is a parameter. I thought about getting this parameter from a data segmentation filter, like the one below, where the user would choose what situation they would like to analyse (that is, the difference in price for products with stock, out of stock, both or the option where that info is not available). How can I achieve this?

PS: I tried following the tutorial in this link: https://blogs.perficient.com/2022/03/14/dynamic-filtering-using-parameters-in-power-bi/. Here, we are advised to create a parameter which is linked to the desired data segmentation filter; then, we use the option "Filter Rows" in power query in the desired column to link the value of the parameter to the values of that column. The screenshot of the tutorial is below (notice the little window at the left of "Country Selection" marked in red - it's to choose the parameter you want):

But when I follow the tutorial, the window that appears to me is the one below, without the option to choose a parameter. Is this a permission issue? A license issue? I feel like this would solve my problem.

Thank you very much!

2 REPLIES 2
Super User

The Parameter drop down in the tutorial you've referenced needs to be created in Power Query. Power BI has two parameters:

* DAX parameters

* Power Query parameters

You could solve your problem using a simple filter for the users, as you've shown in one of your screenshots, but then must use a MEASURE to calculate the diff, because columns do not recalculate based on measure updates. See my Reporting Order of Operations blog post for more info on this: https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html

You can rewrite your column as a new MEASURE, just need to pick which aggregation to use, for example using SUM it would be:

diff = SUMX( my_tableName, my_price / competitor_price - 1 )

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Regular Visitor

Hi Allison, it works fine for calculations like that, but suppose I want to create a column (or measure) to classify the diff values as such:

IF(

diff < 0, "1",

IF(

diff < 0.1, "2",

"3"

)

)

Something like that. I'm afraid I can't do that with a measure, but I need to generalize that idea of creating dynamic columns with filter based values.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.