Forum Discussion

Belle2015's avatar
Belle2015
Icon for Helper II rankHelper II
1 year ago
Solved

Lookup 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...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    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.

     

     

Irwan's avatar
Irwan
Icon for Super User rankSuper User
1 year ago

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.