Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jengwt
Helper V
Helper V

Measures: Getting Around Conflicting Filters and Table Relations

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:

EventCount = CALCULATE(COUNT('Event'[EMPLID])
, CALCULATETABLE('Event'
, '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:

EventCount = CALCULATE(COUNT('Event'[EMPLID])
, CALCULATETABLE('Event'
, '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.

 

 

Capture.PNG

1 ACCEPTED SOLUTION
jengwt
Helper V
Helper V

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:

Fixed.PNG

Thanks for really sticking with the hard questions, Microsoft...

View solution in original post

6 REPLIES 6
jengwt
Helper V
Helper V

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:

Fixed.PNG

Thanks for really sticking with the hard questions, Microsoft...

v-juanli-msft
Community Support
Community Support

Hi @jengwt

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

 

@v-juanli-msft

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.

 

EMPLIDhire dateaction date
110/1/201810/2/2018
210/2/201810/3/2018
310/3/201810/4/2018
410/4/201810/5/2018
510/5/201811/6/2018
610/6/201811/7/2018
710/7/201811/8/2018
810/8/201811/9/2018
910/9/201812/10/2018
1010/10/201812/11/2018

 

Now, look at this case. Here we would expect 6 employees to be counted in October, and 4 in November.

 

EMPLIDhire dateaction date
110/1/201810/2/2018
210/2/201810/3/2018
310/3/201810/4/2018
410/4/201810/5/2018
510/5/201811/6/2018
610/6/201811/7/2018
711/7/201811/8/2018
811/8/201811/9/2018
911/9/201812/10/2018
1011/10/201812/11/2018
v-juanli-msft
Community Support
Community Support

Hi @jengwt

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"

FLAG =
IF (
MAX ( 'Event'[CURRENT_ACTION_DATE] ) <= [maxdate]
&& MAX ( 'Event'[CURRENT_ACTION_DATE] ) >= [mindate],
1,
0
)

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".

 

 

Best Regards

Maggie

 

v-juanli-msft
Community Support
Community Support

Hi @jengwt

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:

If (MONTH([Action_Dates])=10,[EventCount],BLANK())

 

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.

 

Best Regards

Maggie

@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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.