Forum Discussion

TableTopJosh's avatar
TableTopJosh
New Member
1 year ago
Solved

Creating Bounds from Measures

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:

ValueString
71 Week
301 Month
1806 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!

Need Help
  • wardy912's avatar
    wardy912
    1 year ago

    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

2 Replies

  • wardy912's avatar
    wardy912
    Super User
    1 year ago

    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