Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Status Graph with Date Slicer

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 ...
Need Help
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

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

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    Hey thanks so much, this worked like a charm!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

     

    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?