Forum Discussion
Helper VMeasures: Getting Around Conflicting Filters and Table Relations
As I think has been stated, the problem is that the relationship between the date dimension the the Action_Date needs to be ignored. I learned that this can be done using the function USERELATIONSHIP(), in which you explicitely define the relationship you want used.
My new formula reads:
EventCount = CALCULATE(COUNT('Event'[EMPLID]) , USERELATIONSHIP('Event'[CURRENT_HIRE_DATE]
, 'Calendar'[DIM_DATE]
) )And it correctly returns, what at the time of the original postings would have been, 370 for October of 2018 AND has the bars (yellow) in the correct time periods on the chart:
Thanks for really sticking with the hard questions, Microsoft...
Community SupportHi BillyT_350
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 |
| 1 | a | 10/1/2018 | 10/2/2018 |
| 2 | a | 10/2/2018 | 10/3/2018 |
| 3 | a | 10/3/2018 | 10/4/2018 |
| 4 | a | 10/4/2018 | 10/5/2018 |
| 5 | b | 10/5/2018 | 10/6/2018 |
| 6 | b | 10/6/2018 | 10/7/2018 |
| 7 | b | 10/7/2018 | 10/8/2018 |
| 8 | b | 10/8/2018 | 10/9/2018 |
| 9 | c | 10/9/2018 | 10/10/2018 |
| 10 | c | 10/10/2018 | 10/11/2018 |
Best Regards
Maggie
BillyT_350PBI 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 1 10/1/2018 10/2/2018 2 10/2/2018 10/3/2018 3 10/3/2018 10/4/2018 4 10/4/2018 10/5/2018 5 10/5/2018 11/6/2018 6 10/6/2018 11/7/2018 7 10/7/2018 11/8/2018 8 10/8/2018 11/9/2018 9 10/9/2018 12/10/2018 10 10/10/2018 12/11/2018 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 1 10/1/2018 10/2/2018 2 10/2/2018 10/3/2018 3 10/3/2018 10/4/2018 4 10/4/2018 10/5/2018 5 10/5/2018 11/6/2018 6 10/6/2018 11/7/2018 7 11/7/2018 11/8/2018 8 11/8/2018 11/9/2018 9 11/9/2018 12/10/2018 10 11/10/2018 12/11/2018