Filter by multiple dates in the same table

DAX DAX Calculations
1

Hi Enterprise DNA Team,

I am working on a scenerio where I have multiple date columns and I need to use a slicer to filter by 2 of the dates. For the date range a user has selected , the Avg SLA has to be calculated accordingly. Since I can have only 1 active date relationship at a time , I tried using the Userelationship function but it doesnt give me expected results and throws an ambiguity error.

Attaching pbix file and the input file for your reference.

I have an active realtionship with Date1 and its used as another filter. Date2 and Date3 have inactive relationships with Date table.
When a date range of Jan 2019 is selected only application nos 1,3,4 should be considered for the average calculation as the Date2 and Date3 are within the selected date range.

Also I have couple of other dates like Date4,Date5,Date6,Date7 and Date8 in the same table and I need to find the Average SLA between each of them. What would be the optimised way to do this sort of calcuation and which would be the best visual to represent the same.

Thanks in advance.

SLATest.pbix (34.2 KB) SLATest.xlsx (10.5 KB)

1 Like
2

Hello Sim2312,

Simple, you just need to nest the first USERELATIONSHIP function like so:

AvgSLA = CALCULATE(
    CALCULATE(
        AVERAGE('Application'[SLA]),
        USERELATIONSHIP('Application'[Date2],'Table'[Date])),
    USERELATIONSHIP('Application'[Date3],'Table'[Date])

)

Whenever a CALCULATE function contains more than one USERELATIONSHIP function the innermost USERELATIONSHIP function is used so by using a nested CALCULATE function it is able to handle two without conflict or ambiguity. Additionally, up to 10 USERELATIONSHIP functions can be nested.

Hope this helps!
-Brad

SLATest.pbix (38.2 KB)

1 Like
3

Hi Brad,

I never would have thought of that. Thank you so much! It worked like a charm :slight_smile:

  • Sim