Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Vikki
Frequent Visitor

DAX challenge - create measures with grouped values from other table

Hi there,

 

I have a data structure like this:

PBI_Q1.JPG

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 Namestart dateend dateCompletion Date Current PhaseProject Status
P1Phase1Jul-19Jul-20   
P1Phase2Jul-20Aug-21 Phase 2 
P1Phase3Aug-21Oct-22Oct-22 In progress
P2Phase1Jul-20Jul-21 Phase 1 
P2Phase2Jul-21Aug-22   
P2Phase3Aug-22Oct-24Oct-24 In progress
P3Phase1Jan-16Jul-17   
P3Phase2Jul-17Feb-19   
P3Phase3Feb-19Dec-20Dec-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 PhaseProject Status
P1Oct-22Phase 2In progress
P2Oct-24Phase 1In progress
P3Dec-20 Completed

 

Could anyone help?

Thanks ahead,

Vikki

2 ACCEPTED SOLUTIONS
ERD
Community Champion
Community Champion

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])

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

Vikki
Frequent Visitor

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.

Vikki_0-1616700187658.png

Then the measures worked.

Vikki_1-1616700336876.png

Thanks @ERD 

View solution in original post

3 REPLIES 3
Vikki
Frequent Visitor

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.

PBI_P2.jpg

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

Vikki_0-1616690398151.png

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

Thanks again,

Vikki

Vikki
Frequent Visitor

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.

Vikki_0-1616700187658.png

Then the measures worked.

Vikki_1-1616700336876.png

Thanks @ERD 

ERD
Community Champion
Community Champion

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])

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.