Forum Discussion

powerbi2srm's avatar
powerbi2srm
Icon for Resolver II rankResolver II
3 years ago
Solved

Power BI doesn’t recognize a relationship

I have three tables:

- dim_date: a calendar table

- hr_employee: info abouts employees

- hr_contract: info about employee contracts. It's related to hr_employee by "employee_id" and related to dim_date by diffrent date_id fields.

I'm trying to calculate the number of employees in a dynamic way through this DAX measure:

 

num_employees_end_period =
var min_selected_date =
MIN(dim_date[date_id])


var max_selected_date =
MAX(dim_date[date_id])


var result =
CALCULATE(
    DISTINCTCOUNT(hr_employee[employee_id]),
    AND(
        OR(
            hr_contract[max_end_date_id] = 0,
            hr_contract[max_end_date_id] >= min_selected_date
        ),
        hr_contract[min_start_date_id] <= max_selected_date
    )
)


return result

 

 
However, Power BI doesn't recognize hr_contract-hr_employee relationship because when I filter in a slicer by another date the result is the same.
 
It doesn't happen if I join hr_employee and hr_contract in a unique table (all_info) and I use a same code:
 

 

num_employees_end_period =
var min_selected_date =
MIN(dim_date[date_id])

var max_selected_date =
MAX(dim_date[date_id])

var result =
CALCULATE(
    DISTINCTCOUNT(hr_employee[employee_id]),
    AND(
        OR(
            hr_contract[max_end_date_id] = 0,
            hr_contract[max_end_date_id] >= min_selected_date
        ),
        hr_contract[min_start_date_id] <= max_selected_date
    )
)

return result

 

 
You can see the difference in results of both DAX measures in the next two images:

 


 Thank you so much!!

 

Need Help
  • AmiraBedh's avatar
    AmiraBedh
    3 years ago

    The issue you're experiencing is that the relationships and filter contexts are not being propagated through the model in the way you expect. DAX filter context is all about relationships and how tables relate to one another.

    Given the model you've described, you have the following relationships:

    1. `hr_employee` and `hr_contract` are related via `employee_id`.
    2. `hr_contract` and `dim_date` are related via multiple date IDs.

    The issue with the measure using `hr_employee` and `hr_contract` as separate tables is that, in DAX, the filter context doesn't just "jump" across tables. The relationship between `dim_date` and `hr_contract` doesn't mean that a change in the date selection will automatically affect the `hr_employee` table, which is why you're seeing a constant value.

    On the other hand, when you have a single table (`all_info`), all of the fields are in the same table, so filter contexts apply more intuitively to your formula.

    Double check that `hr_employee` is related to `hr_contract` on `employee_id` and `hr_contract` has relationships to `dim_date` on the appropriate date ID fields.
    Modify your measure to incorporate the relationships between tables. This may mean ensuring that your logic takes into account the relationships.

     

    num_employees_end_period =
VAR min_selected_date = MIN(dim_date[date_id])
VAR max_selected_date = MAX(dim_date[date_id])

VAR ContractsWithinPeriod =
FILTER(
hr_contract,
OR(
hr_contract[max_end_date_id] = 0,
hr_contract[max_end_date_id] >= min_selected_date
) &&
hr_contract[min_start_date_id] <= max_selected_date
)

VAR result =
CALCULATE(
DISTINCTCOUNT(hr_employee[employee_id]),
ContractsWithinPeriod
)

RETURN result

     

3 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    3 years ago

    The issue you're experiencing is that the relationships and filter contexts are not being propagated through the model in the way you expect. DAX filter context is all about relationships and how tables relate to one another.

    Given the model you've described, you have the following relationships:

    1. `hr_employee` and `hr_contract` are related via `employee_id`.
    2. `hr_contract` and `dim_date` are related via multiple date IDs.

    The issue with the measure using `hr_employee` and `hr_contract` as separate tables is that, in DAX, the filter context doesn't just "jump" across tables. The relationship between `dim_date` and `hr_contract` doesn't mean that a change in the date selection will automatically affect the `hr_employee` table, which is why you're seeing a constant value.

    On the other hand, when you have a single table (`all_info`), all of the fields are in the same table, so filter contexts apply more intuitively to your formula.

    Double check that `hr_employee` is related to `hr_contract` on `employee_id` and `hr_contract` has relationships to `dim_date` on the appropriate date ID fields.
    Modify your measure to incorporate the relationships between tables. This may mean ensuring that your logic takes into account the relationships.

     

    num_employees_end_period =
VAR min_selected_date = MIN(dim_date[date_id])
VAR max_selected_date = MAX(dim_date[date_id])

VAR ContractsWithinPeriod =
FILTER(
hr_contract,
OR(
hr_contract[max_end_date_id] = 0,
hr_contract[max_end_date_id] >= min_selected_date
) &&
hr_contract[min_start_date_id] <= max_selected_date
)

VAR result =
CALCULATE(
DISTINCTCOUNT(hr_employee[employee_id]),
ContractsWithinPeriod
)

RETURN result

     

  • sergej_og's avatar
    sergej_og
    Icon for Super User rankSuper User
    3 years ago

    I only see inactive relationships between dim_date and other tables on your provides screen snippet. Inactive relationships need a special treatment in DAX.
    Is there a working (active) relationship between dim_date and the other both tables - ecpecially to hr_employee?