DAX challenge - create measures with grouped values from other table

[Vikki]

Hi there,

I have a data structure like this:

Each project has 3 phases. If list everything, the table is like this (I added 3 calculated columns to 'Project Phase' table, with a bunch of blank rows):

Project#	Phase Name	start date	end date	Completion Date	Current Phase	Project Status
P1	Phase1	Jul-19	Jul-20
P1	Phase2	Jul-20	Aug-21		Phase 2
P1	Phase3	Aug-21	Oct-22	Oct-22		In progress
P2	Phase1	Jul-20	Jul-21		Phase 1
P2	Phase2	Jul-21	Aug-22
P2	Phase3	Aug-22	Oct-24	Oct-24		In progress
P3	Phase1	Jan-16	Jul-17
P3	Phase2	Jul-17	Feb-19
P3	Phase3	Feb-19	Dec-20	Dec-20		Completed
…
…

What I'd like to do is to create 3 measures to get each project's [Completion Date], [Current Phase], and [Project Status], the desired outcome should like this:

Project#	Completion Date	Current Phase	Project Status
P1	Oct-22	Phase 2	In progress
P2	Oct-24	Phase 1	In progress
P3	Dec-20		Completed

Could anyone help?

Thanks ahead,

Vikki

[ERD · Accepted solution]

Hello @Vikki ,

You can use LASTNONBLANK (<ColumnName>, <Expression>) function for all 3 measures. Example:

#Current phase = 
LASTNONBLANK('Project Phase'[Current Phase], 'Project Phase'[Current Phase])

[Vikki]

Hi @ERD ,

Thank you for the solution. I have tried this function, it worked with excel file as source.

However, I am having a new issue. Because my work is sourced from SQL D/B. I am not sure if this has anything to do with the problem. The 'Project" diemnsion and "Project Phase" dimension are joined through a fact table, there are other dimension tables having relationship with this fact table. Anyway, I tried exactly the same function measures.

Completion Date = LASTNONBLANK('PROJECT PHASE'[Completion Date], 'PROJECT PHASE'[Completion Date])

Current Phase = LASTNONBLANK('PROJECT PHASE'[Current Phase], 'PROJECT PHASE'[Current Phase])

Project Status = LASTNONBLANK('PROJECT PHASE'[Project Status], 'PROJECT PHASE'[Project Status])

But the measures for all projects are giving the same values. And it looked like the measures are being summarized, because those are the LAST NO BLANK values.

When I use the calculated columns, I am able to set the field to "Don't summarize"

Could someone tell me how to make the measure not being summarized?

Thanks again,

Vikki

[Vikki · Accepted solution]

Actually I figured out how to solve this issue. So just share it here with everyone. The trick is in Manage relationship: Change the cross filter direction from single to both, for both dimension tables to fact table.

Then the measures worked.

Thanks @ERD 

[ERD · Accepted solution]

Hello @Vikki ,

You can use LASTNONBLANK (<ColumnName>, <Expression>) function for all 3 measures. Example:

#Current phase = 
LASTNONBLANK('Project Phase'[Current Phase], 'Project Phase'[Current Phase])