help

[davidibi4524]

hi

i have two tables:

Grouped forecast and

Forecast lookup

grouped forecast looks like this:

Month-year	SUM	year	database
62021	1500	2021	MTR
72021	2000	2021	MTR
82021	120	2021	MEX
92021	200	2021	MEX

And forecast lookup looks like this:

Fmonth-year	Month	Database
12021	1	MTR
22021	2	MTR
32021	3	MEX

Then, in the lookup forecast table i have two "lookupvalue" columns 

from grouped forecast, one for the "mex" database and other for "mtr" database.

(I have to leave the LOOKUPVALUE for all sorts of reasons)

i am looking for a solution to filter the lookupvalue columns and aloow to choose wich database the user want to see the data for..

i created a table called "DATABASE" and contain "mtr" and "mex"

and i created a relationship between "DATABASE" and "GROUPED FORECAST" but its not effect the "LOOKUPVALUES" columns.

thanks 🙂

[v-jingzhang · Accepted solution]

Hi @davidibi4524 

If your forecast lookup table looks like this:

You can create a measure to display the value based on which database is selected in a slicer.

Display Value = 
SWITCH (
    SELECTEDVALUE ( DATABASE[database] ),
    "MTR", SELECTEDVALUE ( 'forecast lookup'[MTR value] ),
    "MEX", SELECTEDVALUE ( 'forecast lookup'[MEX value] )
)

If your forecast lookup table looks like grouped forecast table which has a column for database name and another column for corresponding value, you can create a relationship between DATABASE table and forecast lookup table on database columns. Then the database slicer is able to filter it according to the selected database.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[MFelix]

Hi @davidibi4524 ,

In this setup believe that the best option is to:

• Add a date column on both of the tables that would replace the Month-year column
• Add two dimension tables to your model
  • Calendar table
  • database table (already created)
• Make a one to many relationship between the previous tables and the two table you already have 
• Now you can create all sort of calculations based on this using measure and the two dimension tables in your visualizations no need for lookup