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, I'm not sure if the subjects reflects my question perfectly but I want to have a dynamic dimension based on the slicer.
A small example below:
One dimension table with two projects:
On dimension table with historical info on the phase status of the project
A fact table with the cost of the project
I created two measures: one for the amount filtered on the date slicer and one to get the last status (phase) of the project:
The challange I'm facing is when not having the project on the visual. How can I get the amount current phase in total without the project. Currently it would show:
This absolutly makes sense as the measure looks at the max phase which is in this case 2 but it's not what i want to achieve. Please help me.
What do you want to achieve? This is the question...
This sentence:
"The challange I'm facing is when not having the project on the visual. How can I get the amount current phase in total without the project."
makes no or little sense to me.
What should Amount m be when there's no filter on Project code? When there are many projects visible in the current context, there is no current phase since projects can be in different phases at the same time.
Best
D
What I want to achieve is the total amount on the projects on the phase at the selected period. So if I'm selecting January it should show Phase 1 400 (both projects are on phase 1). If I select Februari the result should be Phase 1 200 (project 1001 total cost is on phase 1) and phase 2 total of 600 (which relates to project 1002). So it should not show the phase on which the cost is booked on the project but the phase it currently relates to:
Result:
January (slicer)
Phase 1 400
Total 400
February (slicer)
Phase 1 200
Phase 2 600
Total 800
After struggling behind my laptop I switched to the front 🙂 and I looked at your solution which is exacly what I was seeking. Thank you very much. This will help me for sure.
darlove, I'm trying to include your solution in my model and I have one more challenge.
I have the phase not in the same table as the costs. The fases are in a dimension table and not in the fact table.
Any idea to overcome this challenge in your suggested solution?
Thanks in advance SnoekL
You have helped me very much which is appreciated.
But now I have the next challenge.
What if I would want to show on one slide the values of previous month ,the values of current month (in seperate table) and the movement. I created a slicer for the month and am using a filter on the min date and the max date on two tables but both the tables will show the last value of the phase. How can I have the previous month get to show the value of the phase of that was applicable at the end of previous month?
Can you instead just use your Phase column in that visual?
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That would always give the the current (last available) phase but I want it to be dynamic based on the slicer. So the project phase should be the status that is applicable at the date selected in the slicer.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
32 | |
30 | |
18 | |
18 |