Forum Discussion

davidibi4524's avatar
davidibi4524
Frequent Visitor
5 years ago
Solved

help

hi

i have two tables:

Grouped forecast and

Forecast lookup

 

grouped forecast looks like this:

Month-yearSUMyeardatabase
6202115002021MTR
7202120002021MTR
820211202021MEX
920212002021MEX

And forecast lookup looks like this:

Fmonth-yearMonthDatabase
120211MTR
220212MTR
320213MEX

 

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 🙂

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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
  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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.