Data modelling question

[Anonymous]

Hi,

I have one specific requirement in Power BI that I am unable to figure out.

I have an 'Employees' table in my data model which consists of the following columns and sample data,

In the 'reportsTo' column, we have employee IDs of the managers, but instead of IDs, I want the names of the managers to show up (in a different column).

In SQL, I can achieve this by using self join to connect the Employees table to itself on the condition reportsTo = employeeNumber.

How can I achieve the same results using Power BI?

Thanks,

Sourav

[TomMartens · Accepted solution]

Hey @Anonymous ,

my table looks like this:

I use this DAX statement to create a calculated column:

reportsto name = 
var reportsto_id  = 'Table'[reportsto]
return
CALCULATE(
    FIRSTNONBLANK('Table'[name] , 0) 
    , FILTER(
        ALL('Table')
        , 'Table'[id] = reportsto_id
    )
)

Now the table looks like this:

Hopefully this is what you are looking for.

Regards,

Tom

[Anonymous]

Hi Tom,

Thanks for your help, the formula is working 🙂.

I was just wondering why the formula is not working without defining a 'var' and using 'return'. Is it not possible to achieve the same resultset by using only CALCULATE or some other DAX function? Just curious.

Thanks,

Sourav

[TomMartens]

Hey @Anonymous ,

you have to be aware that creating a calculated column always starts with a Row Context - the current row, using CALCULATE always transforms the values of the current row into a filter context, this step during the evaluation of CALCULATE is called context transition. As soon as context transition happens it's no longer possible to access the values from the outer row context without special consideration. This can be achieved by either using variables that are defined before context transition happens or using the function EARLIER(...).

I'm using variables in this example for the following reasons:

• personally, I find the usage of variables more readable than using EARLIER
• using variables outside a table iterator function like FILTER(...) allows the caching of values, anf for this reason performs faster than using the function EARLIER that will be evaluated every iteration.

Hopefully this adds to your understanding why I have been using variables.

Regards,

Tom