Creating Bounds from Measures

[TableTopJosh]

Hello,

I have a system of tables, including:

- UNITS (Unit No, Last Maintenance Date)

- FAILURES (Unit No, Failure Date, Failure Info, Subsystem, # Days Since Failure)

- DATE_RANGES (Value, Description)

- DATE (Std Date Table)

I am trying to create a Matrix that displays the Count of "Failures" in each "Subsystem" for a selected "Unit No" in a specific date range OR specific "# Days Since Failure".

I would like to use a Tile Slicer with Single Select to define the date ranges.

DATE_RANGES is as follows:

Value	String
7	1 Week
30	1 Month
180	6 Months
365	1 Year

The slicer returns the selected value in Measure "Date_Range_Value".

I would like to use the slicer to filter the values of FAILURES based on the "# Days Since Failure", with the lower bound being 0, and the upper bound being "Date_Range_Value".

I've already tried creating a measure in the format Filter = IF(MAX(FAILURES[# Days Since Failure]) >= 0 && MAX(FAILURES[# Days Since Failure]) <= [Date_Range_Value], 1, 0) and filtering the visual based on the value of "Filter" = 1, as some older threads suggested. That does not seem to work.

Also open to defining a date range based on a Measure "Unit Last Maintenance Date" and "Range End Date". I have those calculating correctly, providing a beginning and end date.

Any help would be appreciated.

Thank you!

[wardy912 · Accepted solution]

Hi @TableTopJosh 

 Here's 2 measures you can try, one using your date_range_value and the other using actual dates. There's not much difference between the 2, so it's just personal preference.

Failures in Range = 
VAR SelectedRange = [Date_Range_Value]
RETURN
CALCULATE(
    COUNTROWS(FAILURES),
    FAILURES[# Days Since Failure] >= 0,
    FAILURES[# Days Since Failure] <= SelectedRange
)

Failures in Date Range = 
VAR StartDate = [Unit Last Maintenance Date]
VAR EndDate = [Range End Date]
RETURN
CALCULATE(
    COUNTROWS(FAILURES),
    FAILURES[Failure Date] >= StartDate,
    FAILURES[Failure Date] <= EndDate
)

Please let me know if this helps by giving a thumbs up and marking as solved! Thanks

[TableTopJosh]

Worked perfectly. Thank you so much!