Forum Discussion

BillyT_350's avatar
BillyT_350
Icon for Helper V rankHelper V
7 years ago
Solved

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 cur...
Need Help
  • BillyT_350's avatar
    BillyT_350
    7 years ago

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

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi BillyT_350

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

  • BillyT_350's avatar
    BillyT_350
    Icon for Helper V rankHelper V
    7 years ago

    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?