12-13-2018 01:14 PM - last edited 4 hours ago
I'm having some issues getting a measure to return the number that I want.
I have three tables: a calendar table, a table with details on current employees, and a table with events pertaining to current and past employees.
Most the of the metrics that I have based on the data in the historical events tables are based on the date on which that even occurred (action_date). Consequentially, I have that linked as the related column to the calendar table off of which time-related metrics and run chart axes are based (see screenshot).
Of the many vizzes that I have, one requires that I sum the occurrences of a particular event by the hire dates of employees. I have tried a couple of different ways to get this calculation. I know from querying our DB directly that 370 employees who were hired in October 2018 have since experienced a particular event. 87 of these occurred in October.
(1) Method 1:
, 'Event'[CURRENT_HIRE_DATE] IN ALLSELECTED('Calendar'[DIM_DATE])
-- This return is way too high. It returns like 1000 events in October. Not even remotely close, and I have no idea how it's getting that number. Returns 87 if use FILTER instead of CALCULATETABLE.
(2) Method 2:
EventCount= VAR DMIN = MIN('Calendar'[DIM_DATE]) VAR DMAX = MAX('Calendar'[DIM_DATE]) RETURN CALCULATE(COUNT('Event'[EMPLID]) , CALCULATETABLE('Event'
, 'Event'[CURRENT_HIRE_DATE] >= DMIN
&& 'Event'[CURRENT_HIRE_DATE] <= DMAX)
-- Only returns the 87 that occurred in October. Same result if use FILTER instead of CALCULATETABLE.
(3) Check to make sure I'm not crazy:
, 'Event'[CURRENT_HIRE_DATE] >= DATE(2018,10,1)
&& 'Event'[CURRENT_HIRE_DATE] <= DATE(2018,10,31)
-- This method correctly returns 370, as one would expect, but it appears to parse those counts out across the months in which the Action_Dates occur. See the chart below. We do not want this. We want one bar for October saying 370.
Any idea why this is occurring? I would seem that PBI know what I want it to do, but for some reason it's thinking that I still want to use the Action_Date as determining when the event occurred. It's almost like I need a second, invisible x axis based on the Current_Hire_Date.
And, no, I will not duplicate the query for one viz. That table has hundreds of thousands of rows in it.
I would appreciate any ideas or insight the community might have on this issue. Thanks.
12-14-2018 12:19 AM
In your third step,
“it appears to parse those counts out across the months in which the Action_Dates occur. See the chart below. We do not want this. We want one bar for October saying 370.”
Do you want to show only the bar for October in Axis (Action_Dates), for other dates, there is no bar.
If so, you could create a measure like:
From your information, it is not clear for me to understand.
As you said, “370 employees who were hired in October 2018 have since experienced a particular event. 87 of these occurred in October”, I think 370 should be total employees, 87 should be these occurred in October, and they all refer to the numbers of employees.
But in the three methods, you count it from 'Event'[EMPLID],this means count the number of events, also you say 370 is for October.
It is so confused for me to understand.
12-14-2018 06:49 AM
@v-juanli-msft We are only using the numbers for October as an example of what is happening. However, you are correct; we want those 370 events to be shown in October, rather in one of the three months since then.
However, the x axis is based on the distinct dates in the calendar table, not on the event or hire dates in the events table.
And we are using a COUNT(EMPLID) because there are Employee IDs attached to those events.
Does this answer your questions?
12-19-2018 12:20 AM
If you add "date" column from the "Calendar" table in the slicer,
Please try these measures:
maxdate=MAX(Calendar table[Date]) mindate=MIN(Calendar table[Date])
EventCount = CALCULATE ( COUNT ( 'Event'[EMPLID] ), CALCULATETABLE ( 'Event', 'Event'[CURRENT_HIRE_DATE] >=[mindate] && 'Event'[CURRENT_HIRE_DATE] <= [maxdate] ) ) // or you could replace the "CALCULATETABLE" with "FILTER"
MAX ( 'Event'[CURRENT_ACTION_DATE] ) <= [maxdate]
&& MAX ( 'Event'[CURRENT_ACTION_DATE] ) >= [mindate],
If you "We want one bar for October saying 370", (i think you only need one bar shown on the chart)
Add [FLAG] in the Visual level filter, set "show items when value is 1".
12-19-2018 12:21 AM
If my lastest post doesn't help you, please look into this post.
I don’t understand:
“370 employees who were hired in October 2018 have since experienced a particular event. 87 of these occurred in October.”
I make a test to show my understanding, if I’m incorrect, please point out.
In this example,
“10 employees who were hired in October 2018 have since experienced a particular event. 3 (distinct count of events) of these occurred in October.”
|customer id||event||hire date||action date|
12-19-2018 09:10 AM
PBI isn't happy about the criteria for that CALCULATE: "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
I tried using FILTER, and playing with the syntax, but the problem remains.
What exactly does that FLAG measure do? I'm afraid that I won't be able to use it anyway, at least not in this viz, since it alters data in the other columns. I'll try putting it in a seperate viz and see if it works.
As for your example, it's not quite what I'm looking for. In this case, we have already filtered the different types of events.
Let's say that this type of event was type A. In your sample data, 4 customers experienced event A. All four of these were hired in October, so we would expect all four of them to be counted in October.
Now, let's try a different example. In the below case, all 10 employees were hired in October, and have since experienced the event. We would expect all 10 of these to be counted in October.
|EMPLID||hire date||action date|
Now, look at this case. Here we would expect 6 employees to be counted in October, and 4 in November.
|EMPLID||hire date||action date|