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 (
            Sales,
            Sales[CustomerKey] = Customer[CustomerKey] &&
            Customer[Age] >= 20
        ),
        IF (
            Customer[Age] <= 45,
            Sales[Quantity] * Sales[Net Price],
            Sales[Quantity] * Sales[Unit Price]
        )
    )
)

 

 

 

Now I don't need the first FILTER part. But if I remove that  Sales[CustomerKey] = Customer[CustomerKey] won't work anymore. The question is how do I define this relationship without the first FILTER. I could just set a filter that is always true but I'd like to learn how to properly do it.

 

Kind regards,

Need Help

2 Replies

  • 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.