Forum Discussion
How to count things on two different dates
- 1 year ago
Nothing like a good ski trip to help clear the mind and think through the problem.
I realized I needed to get into a context where I had a row for each state. I.E. each project needed a row for created and a row for closed (if it was closed). Then all the rows could have a status column with either the value of 'Created' or 'Closed', thereby giving me my dimension for the legend.After trying a couple of different things, I ended up with this calculated table, and the performance is pretty good, since it only has a filter and it is not actually calculating any new values.
VAR _closedProj = CALCULATETABLE( SELECTCOLUMNS( Projects, "Date", Projects[Date Closed], "project_id", Projects[region.project_id], "Status", "Closed" ), KEEPFILTERS( TREATAS( {"Closed"}, Projects[dRofus Admin Status] )) ) VAR _createdProj = SELECTCOLUMNS( Projects, "Date", 'Projects'[Created Date], "project_id", 'Projects'[region.project_id], "Status", "Created" ) RETURN UNION(_createdProj, _closedProj)
Nothing like a good ski trip to help clear the mind and think through the problem.
I realized I needed to get into a context where I had a row for each state. I.E. each project needed a row for created and a row for closed (if it was closed). Then all the rows could have a status column with either the value of 'Created' or 'Closed', thereby giving me my dimension for the legend.
After trying a couple of different things, I ended up with this calculated table, and the performance is pretty good, since it only has a filter and it is not actually calculating any new values.
VAR _closedProj =
CALCULATETABLE(
SELECTCOLUMNS(
Projects,
"Date", Projects[Date Closed],
"project_id", Projects[region.project_id],
"Status", "Closed"
),
KEEPFILTERS( TREATAS( {"Closed"}, Projects[dRofus Admin Status] ))
)
VAR _createdProj =
SELECTCOLUMNS(
Projects,
"Date", 'Projects'[Created Date],
"project_id", 'Projects'[region.project_id],
"Status", "Created"
)
RETURN
UNION(_createdProj, _closedProj)