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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.