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

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