Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX Project Table with Date Slicer

Hello,

 

I have data with the following structure: 

The only columns that matter are Key, StatusCreated, and Status.

The key is the name of the project, and as it moves through different statuses, a new entry is created with a different status, and the date at which that status was achieved is also recorded. 

 

I want to create a report which will have 3 parts: a pie graph, a table, and a Date slicer. For any given date on the slicer, I want the pie graph to display the breakdown of how many projects are in each status, and table to list the keys of each project and what status they are in at that date. See below for what it should look like:

 

The pie graph seems to be working. I used the solution that Greg_Deckler supplied me, with some small modifications. Here is the DAX which creates the measure for the graph:

Measure Status for Burn Down =
VAR __Status = MAX('Statuses'[Status])
VAR __Table =
ADDCOLUMNS(
SUMMARIZE('DD Statuses Full',[key],"__MaxDate",calculate(MAX([StatusCreated]),filter('DD Statuses Full','DD Statuses Full'[StatusCreated]<= MAX(DateTable[Date])))),
"__Status",MAXX(FILTER('DD Statuses Full',[StatusCreated]=[__MaxDate]),[Status])
)
RETURN
COUNTROWS(FILTER(__Table,[__Status]=__Status))
 
Where DateTable is just a date table which is what the date slicer is based on.
 
 
I am having trouble with the table. This is my best guess as to how to Dax for the table:
 
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]))
 
But this doesn't seem to change with the date slicer. 
 
I may not be understanding how to create tables in DAX, or perhaps I'm not using calculate right. Let me know what you think.
 
Adam
Need Help
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Does anyone have any clues? Have I described the problem properly?

     

    Thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous,

    I think you need to use add a column to remark current status and calculate the duration of current status keeping, then you can use these fields to create a pie chart as you wanted.
    BTW, it will help if you share some dummy data to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng