Skip to main content
cancel
Showing results for 
Search instead 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

Reply
nix_ferreira
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?

 

nix_ferreira_0-1718648871893.png

 

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):

 

nix_ferreira_4-1718649612703.png

 

 

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.

 

nix_ferreira_2-1718649319375.png

 

Thank you very much!

2 REPLIES 2
AllisonKennedy
Super User
Super User

@nix_ferreira 

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 )


Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.