Having properly configured relationships will allow for reporting based upon multiple tables, however there may be cases where relationships are not configured or appropriate, but you still want to include data from a different table in your report.

Method 1 – DAX RELATED Function


If you have relationships configured, then you can create a column or measure using the DAX function RELATED

Right Click on Table to add column and select “New column”

Enter the expression referencing the table data you want to include:

CompanyName = RELATED ( TableName[Field] )

Method 2 – DAX LOOKUPVALUE Function

Another method to include data from another table is to use a DAX Lookupvalue function. This function takes values from the current row and queries another table to return the desired result.

In this example we will take the company ID and use that as the search criteria to return the company name from the companies table.

CompanyName = LOOKUPVALUE(Companies[name], Companies[id], Projects[company], "No Company")

where LOOKUPVALUE(TargetData, TargetSearchColumn, ThisTableColumnToMatch, DefaultValue)

Method 3 – Using Calculate and Filter functions

Here we can match multiple rows and calculate values based upon matching criteria

Sum data from another table =
CALCULATE (
    SUM ( searchTable[column] ),
    FILTER (
        ALL ( table ),
        searchTable[id] = thisTable['invoiceID']
    )
)

Using this function we can calculate the shipping cost from 1 or more line items in an invoice detail table as follows

Spread the love
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •