Forum Discussion
Dynamic Field Parameter Not Working
Hello,
I am somewhat new to Power BI and these forums, but I'll try to convey my issue the best I can.
My goal is to have a dynamic graph for the Y value of each different Supplier Product Size over the span of every Week Year, but when I go to create a field parameter of these values and insert it, any selections made will not work.
The graph on the left is what I want to display with my selection, the right graph is my dynamic parameter graph with the field parameter slicer underneath.
This is what my source data looks like:
-I did have to transpose this data within power query if that is of any signifance.
| Wk Yr | Wk 31 2021 | Wk 32 2021 | Wk 33 2021 | Wk 34 2021 | Wk 35 2021 | Wk 36 2021 | Wk 37 2021 | Wk 38 2021 | Wk 39 2021 | Wk 40 2021 | Wk 41 2021 | Wk 42 2021 | Wk 43 2021 | Wk 44 2021 | Wk 45 2021 | Wk 46 2021 | Wk 47 2021 | Wk 48 2021 | Wk 49 2021 | Wk 50 2021 | Wk 51 2021 | Wk 52 2021 |
| Supplier 125 ML | 3 | 3 | 3 | 5 | 6 | 8 | 8 | 7 | 4 | 2 | 2 | 2 | 4 | 5 | 5 | 4 | 3 | 3 | 3 | 2 | 2 | 5 |
| Supplier 220 ML | 10 | 10 | 8 | 7 | 5 | 3 | 4 | 4 | 5 | 7 | 7 | 6 | 4 | 2 | 2 | 2 | 3 | 6 | 8 | 8 | 8 | 7 |
| Supplier 500 ML | 1 | 1 | 1 | 1 | 2 | 2 | 1 | 1 | 1 | 1 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 0 |
| Supplier 1000 ML | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 |
| Supplier Cap | 10 | 8 | 9 | 10 | 11 | 9 | 11 | 10 | 11 | 12 | 12 | 12 | 12 | 11 | 13 | 13 | 9 | 13 | 11 | 15 | 15 | 15 |
| Supplier 8oz | 6 | 9 | 10 | 9 | 5 | 3 | 4 | 4 | 3 | 3 | 3 | 3 | 2 | 2 | 4 | 5 | 6 | 5 | 4 | 4 | 6 | 10 |
| Supplier 10oz | 1 | 2 | 3 | 2 | 4 | 3 | 3 | 2 | 2 | 0 | 2 | 2 | 2 | 3 | 1 | 2 | 3 | 4 | 6 | 4 | 2 | 1 |
Also a snippet of what my data looks like after Power Query transformation:
I would greatly appreciate any help anyone can give!
Hello cgpahnk ,
My advice would be using unpivot in power query to get the data in the following format:
Once you have the data like that you can insert a slicer referencing the field "Product" so you can filter the product you want to see in the graph
6 Replies
Hello cgpahnk ,
My advice would be using unpivot in power query to get the data in the following format:
Once you have the data like that you can insert a slicer referencing the field "Product" so you can filter the product you want to see in the graph
Okay I might be really picking your brain here, but from these changes are you able to change the color of the column based on whether it exceeded the target or not (Like red for under and green for over)? Even after filtering with new target lines?
Hello, Sorry for the late response.
You can use the following DAX:
And then create a conditional formatting for the columns:
gadielsolis much appreciated! I think this will work. Only issue I have now is ordering the graph by date but I can probably create a date table for that.
Do you think I'd be able to have a dynamic target line that changes for each filter selection?
My advice would be create a separate table with the products and the target for each product (you can create it in excel or within power BI), after that you can connect both tables using the product field and add the target as a line in the grap). Find below some screenshots for reference
Important: In the slicer you must use the product field coming from the targets table since this will be your key value.
