Forum Discussion

Zepox's avatar
Zepox
Frequent Visitor
3 years ago

Define relationship between tables

Hello,   On SQLBI, there is the following code in an article:      Test := SUMX ( FILTER ( Customer, Customer[Country] = "United States" ), SUMX ( FILTER ( ...
Need Help
amitchandak's avatar
amitchandak
Super User
3 years ago

Zepox , if sales and customers already have a join in the model then you do not need that join here.

Zepox's avatar
Zepox
Frequent Visitor
3 years ago

For example here is my situation:

VAR hours =
    SUMX (
        FILTER ( 'order_completion', 'order_completion'[Date] > DATE ( 1900, 1, 1 ) ),
        SUMX (
            FILTER ( 'work_log', 'work_log'[ID] = 'order_completion'[ID] ),
            'work_log'[working_duration] / 'order_completion'[amount_completed]
        )
    )

 

There are 2 tables (order_completion and work_log), both have ID but there is a table between them which connects them. If I remove the top part with the uselss filter I get the following error: a single value column cannot be determined.

 

I need to find the total worked time per item by dividing the total amount of worktime logged in 1 table by the amount of items completed logged in another table. The above code works but obviously is not the cleanest.