Forum Discussion
Lookup values from another table while keeping the visual filter context
Dear All,
I have 3 tables:
FINANCIALS - table containing transactional data for projects (the same project repeats many times, ther are dates for the transaction etc)
PROJECT_NAME - table containing project codes and project names. this table is linked (1-to-many) with FINANCIALS table by a common column - Project Code
PROJECT_BUDGETS - table containing project code and 3 columns: budgeted sales (for each proj code), budgeted costs and forecasted costs.
I built a simple visual (matrix) showing me some numerical values coming from FINANCIALS table by project name.
Now, I would like to add to this visual a column that contains a budgeted costs for each of displayed project names. this at first seamed to be very simple but I got stuck. with the following dilemas:
- I do not know how (and if at all) the PROJECT_BUDGET table should be linked to other two tables
- not linking the PROJECT_BUDGET table and using TRETAS(), I think I am getting what I want but my visual shows now all the projects, irrespectively if they have any financials or not. I would like to get only those project that do have financials -so basically by adding budgeted costs I would not like my visual to change in number of rows (with project names) displayed and be sensitive to any filtering based on FINANCIALS dimensions.
- in excel, if my visula would be a pivot of FINANCIALS, the simplest way to add budgeted costs would be vlookup in the first column not being part of the pivot, referring to project code. I am quite sure it must be as simple in PBI with DAX, but just can not figure out the proper logic to get what I need.
can I ask for your help? thank you in advance
Hi Paul Zheng,
I was trying few solutions. when I linked PROJECT_BUDGET table to FINANCIALS (or linked it to PROJECT NAME, that was linked to FINANCIALS) then in certain scenarios I was getting desired results, however all projects from PROJECT_BUDGET were expanded in the visual.
My ned was to get only those projects that have financials, so only those that are present in FINANCIAL table. this works fine with keeping the tables not linked and using Selectedvalue.
3 Replies
Dear Colleagues,
I needed the solution urgently and worked it out (I think).
The following formula makes its work:
Project Budget =calculate([AMOUNT BUDGET], //this is the amount from the PROJECT_BUDGET tablefilter(all('Project Budget'),'Project Budget'[Project Number] = SELECTEDVALUE(FINANCIALS[Project Number])))
BRDraszor
I am not sure about your model, and Selectedvalue() only works when using unrelated table. However, glad you solved it.Paul Zheng _ Community Support Team
Hi Paul Zheng,
I was trying few solutions. when I linked PROJECT_BUDGET table to FINANCIALS (or linked it to PROJECT NAME, that was linked to FINANCIALS) then in certain scenarios I was getting desired results, however all projects from PROJECT_BUDGET were expanded in the visual.
My ned was to get only those projects that have financials, so only those that are present in FINANCIAL table. this works fine with keeping the tables not linked and using Selectedvalue.
