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.
Hello,
I have data structured as follows:
Project | Status | Date of Status |
ProA | started | January 1, 2020 |
ProA | testing | March 13, 2020 |
ProA | done | April 2, 2020 |
ProB | started | February 14, 2020 |
ProC | started | February 27, 2020 |
ProC | testing | March 20, 2020 |
To be clear, the date column represents when the project reach the given status.
What I want is a to create a report which has a pie graph and a date slicer. Whatever date the slicer is set to, the pie chart displays the number of projects in each status. So for instance, if the date slicer was chosen as March 15th, the graph would have a value of 2 for started, and 1 for testing.
The main issue I'm having is the inability for pbi to do a count of the latest status of each project. I want pbi to know, for instance, that any time between Feb 27 and March 20, that ProC is in the "started" status, and any time between March 20 and now it is in testing.
Let me know if the problem is clear.
Solved! Go to Solution.
@Anonymous I am thinking that you will need a disconnected table of your status codes (use an Enter Data query to enter or use:
New Table = DISTINCT('Table'[Status]). Make sure there are no relationships to this table. Use the column created in this table as your legend. Then do something like the following (below). I mocked up the solution in the attached PBIX below sig. You want Page 18.
Measure 18 =
VAR __Status = MAX('Table (18a)'[Status])
VAR __Table =
ADDCOLUMNS(
SUMMARIZE('Table (18)',[Project],"__MaxDate",MAX([Date of Status])),
"__Status",MAXX(FILTER('Table (18)',[Date of Status]=[__MaxDate]),[Status])
)
RETURN
COUNTROWS(FILTER(__Table,[__Status]=__Status))
@Anonymous I am thinking that you will need a disconnected table of your status codes (use an Enter Data query to enter or use:
New Table = DISTINCT('Table'[Status]). Make sure there are no relationships to this table. Use the column created in this table as your legend. Then do something like the following (below). I mocked up the solution in the attached PBIX below sig. You want Page 18.
Measure 18 =
VAR __Status = MAX('Table (18a)'[Status])
VAR __Table =
ADDCOLUMNS(
SUMMARIZE('Table (18)',[Project],"__MaxDate",MAX([Date of Status])),
"__Status",MAXX(FILTER('Table (18)',[Date of Status]=[__MaxDate]),[Status])
)
RETURN
COUNTROWS(FILTER(__Table,[__Status]=__Status))
Hello @Greg_Deckler , I have a follow up question. You're solution was great, and I want to add something. Besides the pie graph which shows the totals of all the projects, I also want to display a simple table which shows the names of each project, the status, and at what date it got that status. Ideally, I also want the table to be sliced based on status when you click on a piece of the pie graph.
Here is a picture of what I'm going for:
I thought of creating a column in DAX as follows. It is definetely based on what you supplied me before.
Hey thanks so much, this worked like a charm!
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.