DAX measure to do the opposite of USERELATIONSHIP

[joluwa]

I have a DAX code called DateDiff that checks count of days between the campaign start and end date that fall within the calendar date slicer which worked fine.

However, upon doing more testing I noticed it returns an incorrect number due to the direction its travelling in based on the relationship.

DateDiff = 
VAR selectmin =
    MIN ( CalendarTable[Calendar Date])
VAR selectmax =
    MAX (CalendarTable[Calendar Date])
VAR firstDateSelect =
    IF ( selectmin <= [mincheck], [mincheck], selectmin )
VAR secondDateSelect =
    IF ( selectmax >= [maxcheck], [maxcheck], selectmax )
RETURN
         DATEDIFF ( firstDateSelect - 1, secondDateSelect, DAY )

So I need the DAX calculation to travel in the blue arrow direction, as currently when I turn the relationship from the range table and overview table to inactive, the calculation returns the correct result.

Is this possible to do in DAX to force a DAX to travel in a certain direction when both relationships are active ?

[joluwa]

PBIX File: https://drive.google.com/file/d/19K7v0hoXpeNuW9ldENzK65Ht8im9OjcM/view?usp=sharing

I tried using CROSSFILTER but this still returns 104, but it should return 105

[danextian]

Hi @joluwa ,

I've been exploring your pbix but havent been able to wrap my head around what you're trying to achieve.

First, why use a separate dates table when what you're trying to return is simply the max and min  dates in Overview Table? I can't find CalendarTable being used anywhere else. Using min and max on day_id would have returned the same result.

Second, based on the current slicer selections in your pbix, what values do you expect for these? Why 105 and not 104.

[joluwa · Accepted solution]

I managed to sort the issue, I just turned the relationship to inactive and then used the USERELATIONSHIP function in my other DAX measures and the measure which I was originally having trouble with started working as it should

[danextian]

Hi @joluwa ,

You have the option to utilize CROSSFILTER to adjust a relationship within the scope of a measure. If this method doesn't suit your needs, kindly provide a sample dataset that is usable (not an image) along with your anticipated outcome. You can share a link to a PBIX or Excel file with any sensitive information removed via Google Drive, OneDrive, or Dropbox.