Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |