Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
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!
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
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.
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
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |