First record from related table based on few critereas

DAX DAX Calculations
1

I have two tables projects and work log. I need column in project table who is this project designer. So who have first record in work log table with type design. I have relationship One project → many work_log
I wrote such formula:

Designer  = CALCULATE(FIRSTNONBLANK(work_log[Employee];TRUE());
                                                  FILTER(ALL(work_log);AND(work_log[project_id]=Project[id]; work_log[type]="Design")))

But unfortunately column is empty. Any suggestions?

2

@Vytenis,

It’s difficult to diagnose these sorts of problems without seeing the data and data model. Can you please post a PBIX file?

  • Brian
3

@BrianJ for some reasons I could not attach original data, but I made very simple pbx instead. And there my formula seems to be ok. sample.pbix (39.9 KB)

In my original report i have little bit modified it
Designer= CALCULATE( CALCULATE(FIRSTNONBLANK(work_log[employee_id;TRUE());FILTER(work_log;work_log[type]="Design")))
and it start performing as well.

4

@Vytenis,

Looks good – glad you’ve got it working well now.

  • Brian