Lookup to another table?

[Belle2015]

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 table 2 for certain months or not at all. 

What I am trying to do is a matrix chart that will show all the employees listed in table 1 and give the total value and count of expenses submitted for each month that are for expense type company car and if the employee hasnt had any that it shows a zero.

Thanks in advance

Expected Output only with all employees showing and displaying  a 0 for any employees who dont have a company car expense

Table 1

Employee ID	Fuel Type	Company
123456	Petrol	Comp1
123457	Diesel	Comp1
123458	Electric	Comp1
123459	Diesel	Comp1
123460	Diesel	Comp1
123461	Diesel	Comp2
123462	Diesel	Comp2
123463	Electric	Comp2
123464	Electric	Comp1
123465	Electric	Comp1
123466	Petrol	Comp1
123467	Electric	Comp1
123468	Electric	Comp1
123469	Petrol	Comp2
123470	Petrol	Comp2
123471	Diesel	Comp2
123472	Diesel	Comp2
123473	Diesel	Comp2
123474	Diesel	Comp2

Table 2

Employee ID	Expense Type	Transaction Date	Transaction Amount
123456	Company Car	12/03/2025	3.36
123457	Car rental	02/04/2025	3.36
123458	Car rental	17/03/2025	111.44
123459	Company Car	03/04/2025	94.72
123460	Company Car	12/03/2025	96.08
123461	Company Car	02/02/2025	90.72
123462	Company Car	16/03/2025	85.89
123466	Company Car	17/03/2025	91.28
123467	Company Car	18/03/2025	89.95
123468	Hotel	19/03/2025	88.9
123469	Public Transport	20/03/2025	3.92
123470	Company Car	21/03/2025	3.92
123472	Public Transport	16/03/2025	3.92
123473	Public Transport	23/03/2025	3.92

[v-kpoloju-msft]

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!

[Irwan]

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
)

Hope this will help.

Thank you.

[lbendlin · Accepted solution]

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 expense

Try not to do that, instead leave the row blank.

[Belle2015]

thank you, I got it, it was the relationship type 🙂 I was just over thinking it when it wasnt working!

[Belle2015]

Hi, This is what I was trying to do at first and had it show as blank line for the employee's without expenses until I apply the filter for company car, once I apply the filter it does not show the employee ID's without a blank like

[danextian]

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.