cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amausko
Frequent Visitor

Status Graph with Date Slicer

Hello,

 

I have data structured as follows:

 

ProjectStatusDate of Status
ProAstartedJanuary 1, 2020
ProAtestingMarch 13, 2020
ProAdoneApril 2, 2020
ProBstartedFebruary 14, 2020
ProCstartedFebruary 27, 2020
ProCtestingMarch 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.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
Super User IV

@amausko 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))

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Greg_Deckler
Super User IV
Super User IV

@amausko 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))

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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: 

amausko_1-1601415353965.png

I thought of creating a column in DAX as follows. It is definetely based on what you supplied me before.

 

LatestStatus = ADDCOLUMNS(
                                             SUMMARIZE('DD Statuses Full',[key],"__MaxDate",calculate(MAX('DD Statuses'[StatusCreated]),                                                                      (filter('DD Statuses Full','DD Statuses Full'[StatusCreated] <= max(DateTable[Date]))))),
                                             "__Status",MAXX(FILTER('DD Statuses Full',[StatusCreated]=[__MaxDate]),[Status]))
 
Where DD Statuses Full is my table name, key is the name of the project, StatusCreated is the date that the project got a new status, Status is the status, and DateTable is just a date table that the shown slicer is based off of. 
 
The table is not changing when i move the date slicer. Perhaps I have misunderstood how to create tables in DAX, or perhaps I'm using CALCULATE wrong. Any thoughts?

Hey thanks so much, this worked like a charm!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!