Dynamic tables through multiple parameters

[smiley1]

Hi folks. Excuse my terminology if it is not correct, I'm new and learning all the time.

I was wondering if it was possible to do the following.

The user has an area within the report to enter values through a drop down box. Eg:

Road    |     Max Speed

1          |       50

2          |       40

3          |       30

I would like the "Max Speed" amount to be dynamically changed through a drop down or slider or something like that. There are more than 3 "Roads", I just use the above table as an example.

Then when the user has selected a new value for Max speed, either they hit a button, or the report dynamically updates the graphs which use the "Max Speed" value that should be the value taken directly from the "table" (or whatever you want to call it) as above.. The above "table" will need to have some of join or relationship with other tables.

It should work exactly like a vlookup in Excel. You would vlookup from sheet1 to the required value in a table in sheet2. When you change the value in table in sheet2 then the corresponding value in the sheet1 will also change... because they are linked. So I am trying to replicate this behaviour.

I have tried experimenting with parameters found found they seem to be fixed at their default value and cannot dynamically change in real time when the user chooses a different value.

Any help or pointers would be really appreciated.

[smiley1]

Hi and thanks for your reply.

One of the other characteristics of a vlookup in an excel sheet that I don't think has been explored is that in the column to be looked up you can change that value, either by typing in a new value or selecting a new value from a drop down box. Of course you cannot change the value if the workbook / cells are protected but let's not complicate things here, for the purposes of the example you can change the value.  Then when you do the vlookup to that column which now contains a new value the result returned will also change depending on the value that is in the result cell of the lookup.  You can change the value in the cell to be looked up as many times as you want and it will be instantly updated in the cell that contains the vlookup formula - this functionality happens because there is a link between the cell that contains the vlookup formula and the cell that the vlookup refers to. You can change the value in the cell that the vlookup refers to and the result will be different because it has updated based on the value in the cell that is looked up - viola!  That is the aspect that I am trying to replicate here.

The lookup functionality of Power BI and which one of the many ways I can achieve that is not such an issue although it is very useful to learn new ways of doing things so I do appreciate the advice given and am not trying to be awkward, what has been causing the issue is being able to replicate the functionality of updating values in the manner that is described above, in a lookup table provided to the front end user of a report in a dynamic way. So as in my example above you can enter a value into Max Speed. Then using a method of lookup that revised value will then be returned immediately in another part of the report. It could be returned in a table, graph etc etc. The point is that the user can change a value and that changed value will instantly feed through and update other values in the report. It may do that via a colculated column, a calculated measure, a series of joins, or some other way. I am not unfortunately not experienced enough to make a judgement of the best way this could be done as I am not an expert in Power BI.   I am fairy sure it can be done with a mix of parameters and some DAX, unfortunately I am still learning my round Power BI and am not at an advanced stage yet. That is the missing piece I could do with some assistance on.

[Anonymous]

@smiley1,

Could you please share sample data of all your tables and  post expected result here so that we can provide you DAX?

Regards,
Lydia

[Anonymous]

@smiley1,

You can use LOOKUPVALUE() function or RELATED() function to replicate similar VLOOKUP behavior in Excel. And you can check the example in this article .

If you still have questions, please share sample data of your tables and post expected result here.

Regards,
Lydia