Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Data modelling question

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,       ...
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
TomMartens
Super User
6 years ago

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