Power BI DAX, multiple columns from different tables into one table

I need to combine columns from four different tables into one summary table. I have one-to-many relationships between these tables. I want to do this using a DAX formula, currently I can drag the different columns into a table visual and I get the expected result but I want to avoid solving the problem in this manner.

I wrote the following formula, at the moment it only shows the columns from the 'purchase_requisition' table. I want to include one column from 'eng_firms', 'business-unit', and 'user' tables. What I tried so far is commented out. When I try adding the columns from the different tables, it tells me the columns either don't exist or the column does not have a relationship to any table available in the current context.

The reason I have the FILTER at the beginning is because the 'purchase_requisition' table has more than 100,000 rows. This table is from a DirectQuery.

I also tried to SUMMARIZECOLUMNS to replace SELECTCOLUMNS, this gave a different error stating that a single value for column [item_delivery_date_lfdat] cannot be determined.

FilteredTable = 
    FILTER(
        ADDCOLUMNS(
            CROSSJOIN(
                SELECTCOLUMNS(
                    FILTER(purchase_requisition, purchase_requisition[item_delivery_date_lfdat] = TODAY() && purchase_requisition[flag_goods_service] = "Service" && purchase_requisition[deletion_indicator_in_purchasing_document_loekz] <> "X"),
                    "VendorKey", 'purchase_requisition'[desired_vendor_lifnr], -- relationship with 'eng-firms'[Vendor_number]
                    "PlantKey", 'purchase_requisition'[plant_key], -- relationship with 'business-unit'[plant_key]
                    "User", purchase_requisition[name_of_requisitioner_requester_afnam] --relationship with 'user'[user_identifier]
                ),
                SELECTCOLUMNS(
                    'eng-firms',
                    "VendorNumber", 'eng-firms'[Vendor_number],
                    "Firm", 'eng-firms'[Parent]
                ),
                SELECTCOLUMNS(
                    'business-unit',
                    "BuPlantKey", 'business-unit'[plant_key],
                    "BusinessUnit", 'business-unit'[business_unit.1]
                ),
                SELECTCOLUMNS(
                   'user',
                   "UserIdentity", 'user'[user_identifier],
                   "EmailAddress", 'user'[email_address]
                )
            ),
            "ReqNumber", 'purchase_requisition'[purchase_requisition_number_banfn],
            "ProjectTitle", 'purchase_requisition'[short_text_txz01],
            "DeliveryDate", purchase_requisition[item_delivery_date_lfdat],
            "Firm", 'eng-firms'[Parent],
            "BusinessUnit", 'business-unit'[business_unit.1],
            "EmailAddress", 'user'[email_address]
        ),
        NOT ISBLANK([ReqNumber]) && NOT ISBLANK ([ProjectTitle]) && NOT ISBLANK([DeliveryDate]) && NOT ISBLANK([Firm]) && NOT ISBLANK([BusinessUnit]) && NOT ISBLANK([EmailAddress])
    )

I narrowed the code down to the following to test the CROSSJOIN functionality, and Power BI has been working on it for 1+ hour.

Solution

Does your Data Model look like this? Data Model

Is it the desired output that you want to obtain by using calculated table? Desired Output

[Update] Perfect! In this case what you really tries to achieve is adding extra columns to the fact table. We know that fact table is connected to all dimensional table with many-to-one relationship, therefore, there is only 1 corresponding row in each dimensional table that we need. Considering the existance of the relationsip, if your model would be in import mode you could sipmly apply this code to obtain the desired result (i.e. getting the corresponding row from dimensional table instead of using crossjoin() ).

FilteredTable = 
VAR _FilterDate = DATE( 2024, 4, 30 )
VAR _ResultTable =
    SELECTCOLUMNS(
        FILTER(
            purchase_requisition, 
            purchase_requisition[item_delivery_date_lfdat] = _FilterDate && purchase_requisition[flag_goods_service] = "Service" && purchase_requisition[deletion_indicator_in_purchasing_document_loekz] = "X"
        ),
        "ReqNumber", 'purchase_requisition'[purchase_requisition_number_banfn],
        "ProjectTitle", 'purchase_requisition'[short_text_txz01],
        "DeliveryDate", FORMAT( purchase_requisition[item_delivery_date_lfdat], "dd MMMM yyyy" ),
        "Firm", RELATED( 'eng-firms'[Parent] ),
        "BusinessUnit", RELATED( 'business-unit'[business_unit.1] ),
        "EmailAddress", RELATED( 'user'[email_address] )
    )

RETURN _ResultTable

However, the presence of DirectQuery adds to the model "limited relationships" so we can't use Related() fucntion anymore for the creation of calucalted table. Therefore, we replace it with lookup (it's safe to do so because of many-to-one relationship). So the final verison on the code would look like this:

FilteredTable = 
VAR _FilterDate = DATE( 2024, 4, 30 )
VAR _ResultTable =
    SELECTCOLUMNS(
        FILTER(
            purchase_requisition, 
            purchase_requisition[item_delivery_date_lfdat] = _FilterDate && purchase_requisition[flag_goods_service] = "Service" && purchase_requisition[deletion_indicator_in_purchasing_document_loekz] = "X"
        ),
        "ReqNumber", 'purchase_requisition'[purchase_requisition_number_banfn],
        "ProjectTitle", 'purchase_requisition'[short_text_txz01],
        "DeliveryDate", FORMAT( purchase_requisition[item_delivery_date_lfdat], "dd MMMM yyyy" ),
        "Firm", LOOKUPVALUE( 'eng-firms'[Parent], 'eng-firms'[Vendor_number], purchase_requisition[desired_vendor_lifnr] ), 
        "BusinessUnit", LOOKUPVALUE( 'business-unit'[business_unit.1], 'business-unit'[plant_key], purchase_requisition[plant_key] ),
        "EmailAddress", LOOKUPVALUE( user[email_address], user[user_identifier], purchase_requisition[name_of_requisitioner_requester_afnam] )
    )

RETURN _ResultTable

Here are the screenshots of both outputs: Import Mode Direct Query

I hope it helps and good luck with your project!