Forum Discussion
rpiboy_1
Helper V
1 year agoHow to count things on two different dates
Let's say that I have an imported semantic model that has the following tables and columns: Projects ID Name Created Date Closed Date (calculated column after import) Date Table well for...
- 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)
Anonymous
1 year agoNot applicable