Lookup to another table?

Hi 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.

hello Belle2015 

i am not sure but is this what you are looking for?

if yes, then you can add if statement in your expense and count measure to replace blank value into zero.

something like below

Count = 
var _Count = 
CALCULATE(
    COUNT('Table 2'[Transaction Amount]),
    'Table 2'[Expense Type]="Company Car"
)
Return
IF(
    _Count=BLANK(),
    0,
    _Count
)

Expense = 
var _Expense = 
CALCULATE(
    SUM('Table 2'[Transaction Amount]),
    'Table 2'[Expense Type]="Company Car"
)
Return
IF(
    _Expense=BLANK(),
    0,
    _Expense
)

Hi Belle2015,

May i know has your issue been resolved? If the response provided by the super users lbendlin, danextian, Irwan, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!