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 have an issue with my report for total value (to-date). I'ts a simple measure, but it provides duplicate projects because a project has changed status. My table has status in a column, which means that a project with a history of more than one status, then it's going to show up on both statuses. I would like the total value to only show once per project and for the status it has in the selected period. Any suggestions on how to solve this?
I have created a sample pbix file which shows the problem, link here:
https://www.udrop.com/5jCK/Revtodateissue.pbix
Has you can see from the picture, project 1 and 2 shows up twice..
Thanks!
Solved! Go to Solution.
Hi,
Solved it by using this measure:
Revnue to date LastValue =
CALCULATE(
[Revenue to date],
LASTNONBLANK(
'histproject'[period],
[Revenue to date]
)
)
Hi,
Solved it by using this measure:
Revnue to date LastValue =
CALCULATE(
[Revenue to date],
LASTNONBLANK(
'histproject'[period],
[Revenue to date]
)
)
@Anonymous glad you solved it and thanks for the update.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@Anonymous
Which status do you want the project to fall into? You already have Status column in your DimProject table, so use that in your visual instead of ProjHistory[Status] and that will solve your problem. If that's not the status you want, then please specify what you're looking for or why that doesn't work for your requirements.
Cheers,
Allison
PS - I would suggest changing the relationship between DimProjects and ProjHistory to SINGLE cross-filter direction so that you don't accidentally get these duplicate tables in the future.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @AllisonKennedy , I agree that using status from my dimProject tabel will give med the correct latest status. This row status will change. My ProjHistory has the status a project was in for each period, which is what I want, because I would like to use a slicer for months and then see what status the project had in the selected month, if that makes sense.
i.e, if I select period 202104, then project 1 should show up in status 2, but if I select period 202102, then I would like it to show up with status 1.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |