Forum Discussion
Belle2015
Helper II
Helper IILookup to another table?
Hi, I have two tables, Table 1 has the Employee ID and Table 2 has the employee ID, expense type, value and transaction date. Some employees in table 1 may not have had an expense and be listed on ta...
Like this?
Filters should ideally come from the dimension table. Your filter (Expense type) comes from the fact table.
No need for a lookup, let the data model do the work for you
showing and displaying a 0 for any employees who dont have a company car expenseTry not to do that, instead leave the row blank.
danextian
Super User
Super UserHi Belle2015
Try the following:
With Company Car Expense =
-- This calculated column returns TRUE if the employee has a "Company Car" expense in Table2
VAR CompanyCarEmployee =
-- Create a table of unique Employee IDs from Table2 where Expense Type is "Company Car"
SUMMARIZE (
FILTER ( Table2, Table2[Expense Type] = "Company Car" ),
Table2[Employee ID]
)
RETURN
-- For each row in Table1, return TRUE if the Employee ID exists in the filtered list
IF ( Table1[Employee ID] IN CompanyCarEmployee, 1, 0 )
Filter your visual to return 1 for the above calculated column or incorporate the filter in your measure.