Forum Discussion

rpiboy_1's avatar
rpiboy_1
Helper V
1 year ago
Solved

How 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...
  • rpiboy_1's avatar
    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)