Forum Discussion

rpiboy_1's avatar
rpiboy_1
Icon for Helper V rankHelper 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
    rpiboy_1
    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)

     

     

some_bih's avatar
some_bih
Icon for Community Champion rankCommunity Champion
1 year ago

Hi rpiboy_1 for part Closed Date, did you try to do creation of calculated column and logic applied in Power Query part?
Check also usage of DAX function USERLATIONSHIP on link and this link.

  • rpiboy_1's avatar
    rpiboy_1
    Icon for Helper V rankHelper V
    1 year ago

    I can calculate the count of closed projects with USERELATIONSHIP just fine. The issue is that typically in a bard chart, you have a single calculated value, that you then split into multiple bars based on a dimension. In this case the dimension is Creates or Closed, the status of which is based on the existence of said dates, so I'm counting two different dates on the same set of rows. Projects that are 'closed' will also have a 'created' date, which means they're effectively counted twice, once in each context.