Filter Measure Between Dates

[Anonymous]

I have data which has a from and to date, with the date it is created in the from and the date its no longer active in the to column. I created a filter measure so I can selected a date from a disconnected date table, and if the risk created date is before the selected date and the end date is after then show the data. Also if the risk is still ongoing the to date is blank (null).  This is the measure I created;

Active Threats = IF([Selected Date]>MAX('Risk Data'[FromTT])&&IF(ISBLANK(MAX('Risk Data'[ToTT])), TRUE(), [Selected Date]<MAX('Risk Data'[ToTT])), 1, 0)

I then filter my table using this measure.

This is my data, I'm expected when I filter the date to before the 16th of jan, for the 1 extreme and 1 moderate risk to drop off.

However this only happens if i keep the date column in there, otherwise all the moderate risks drop off. Any ideas why this is happending? Test data Here: https://drive.google.com/file/d/1iEN5Iz5U9h0pEPf_bRZw9x3LFeGTClYC/view?usp=sharing

[Anonymous · Accepted solution]

I managed to created a count measure that works as I would like;

Count of Active =

VAR __SelectedDate = [Selected Date]

Return

CALCULATE(COUNT('Risk Data'[Id]),__SelectedDate>'Risk Data'[FromTT], IF(ISBLANK('Risk Data'[ToTT]), TRUE(), __SelectedDate<'Risk Data'[ToTT]))

But I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID

[v-lili6-msft · Accepted solution]

hi @Anonymous 

---

@Anonymous wrote:

I managed to created a count measure that works as I would like;

Count of Active =

VAR __SelectedDate = [Selected Date]

Return

CALCULATE(COUNT('Risk Data'[Id]),__SelectedDate>'Risk Data'[FromTT], IF(ISBLANK('Risk Data'[ToTT]), TRUE(), __SelectedDate<'Risk Data'[ToTT]))

But I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID

---

I have test on my side, This formula works well, and what is " I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID"?

Regards,

Lin

[kentyler]

Sad to say, I cannot figure out exactly what is going on.

I am posting the version of your measure I rewrote with as many VARs as possible

Active Threats =

VAR max_date = MAX('Risk Data'[FromTT])

var sel_date = [Selected Date]

var is_selected = [Selected Date]>max_date

var is_blank = ISBLANK(max_date)

var false_test = [Selected Date] < max_date

var result = IF(is_selected && IF(is_blank, TRUE(), false_test), 1, 0)

return result

I tried returning the different vars to figure out what the problem was, but could not see what what happening.

[Anonymous]

@kentyler Thanks for taking a stab at it, I think what is going on is that when I only have the risk and count in the table, date filter inside my measure is using the 16th of jan for all moderate risks since it uses MAX(date) its picking up the one out of date range for all moderate risks. Not too sure how I would edit my measure to evaluate row by row though.

[kentyler]

your selected date measure is reading from the slicer, and if the slicer does not have a value it defaults to the max of the date table... you can test this by just hitting the eraser on the slicer and removing any selection from it.

Selected Date = SELECTEDVALUE('Date Table'[day_date], MAX('Date Table'[day_date]))
so you need to think about how you want your measure to work if there is no date range selected.

[kentyler]

this is what I get when the date slicer is blank 

you seem to only be using one of your date columns, so each risk is attached to a single date... have you considered creating a relationship between that date column and your date table and just letting the slicer do the work.

all the risks show up, even without the date column being included

[Anonymous]

The issue with creating a relationship for the date is that I have several other tables with similar data with from and to date ranges, that all relate to each other, so I cant relate the date table to each other without creating circular references

[kentyler]

I would be curious to see the relationship diagram

If you think about your several tables as fact tables... they should all be able to be joined by a single date table that had a one to many relationship to all of them

If you want to relate the the same date column in each table.

If you want to also relate the other date column, the one you don't seem to be using in your example, I can see that you might need to then have a second date table to relate to all of those columns. Then any filters or slicers should work on all the "fact" tables independantly.
The other option might be.. and I'm reaching here. To have tables which had the error condition and one record for each day it was operative. Then answering questions like, how many errors of each type were open on a certain day would be straightforward, without having to do a lot of equal to or less than style of logic.
I'd love to have a more extended conversation with you about the whole problem.

[Anonymous]

@kentyler 

The six highlighted tables each have from and to dates.

[kentyler]

A lot of them seem to be history tables... are they all histories of the same event ?

[Anonymous]

The Data is for BowTie Reporting. We have different hazards at our company, i.e. Vehicle accidents. Each of these hazards have different threats and consequences, example threats being crash as the result of employee intoxication, or crash as a result of third-party error. Different consequences being cost of destroyed vehicles, physical injury, loss of life. These threats and consequences have controls to mitigate these risks, such as license checks to take out company cars, sobriety checks, insurance to mitigate financial loss. All of these have different levels of severity and likelihood allocated to them, such as a crash resulting in loss of life being high severity and low likely hood, or a fender bender being moderate likelihood, low severity. These likelihoods are determined by the efficacy of the controls to prevent them such as the sobriety check and license reducing the likelihood of occurrence, so when new check are added in, likelihoods change, or if we implement more effective controls. I want to be able to see the number of risks over time, how effectively we've been covering them etc.

[Anonymous]

So there is a table for hazards, table for risk, one for consequneces, one for controls for risks and one for consequnece etc.

Thus each table has dates for when new risks are added and removed, when we've implemented controls for those risks, how serverity and likelihoods change over time, and whatnot

[Anonymous]

I have checked the conversation...the measure you have create is correct. Just add that measure to visual level filter and set it to TRUE.

If facing difficulties then replace those true false with 1 0 in measure and add set visual level filter to 1.

Thanks
Pravin

If it resolves your problem mark it as a solution and give Kudos.

[Anonymous]

Could you show me which measure is the correct one? I'm not too sure which one you mean.

[Anonymous · Accepted solution]

I managed to created a count measure that works as I would like;

Count of Active =

VAR __SelectedDate = [Selected Date]

Return

CALCULATE(COUNT('Risk Data'[Id]),__SelectedDate>'Risk Data'[FromTT], IF(ISBLANK('Risk Data'[ToTT]), TRUE(), __SelectedDate<'Risk Data'[ToTT]))

But I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID

[v-lili6-msft · Accepted solution]

hi @Anonymous 

---

@Anonymous wrote:

I managed to created a count measure that works as I would like;

Count of Active =

VAR __SelectedDate = [Selected Date]

Return

CALCULATE(COUNT('Risk Data'[Id]),__SelectedDate>'Risk Data'[FromTT], IF(ISBLANK('Risk Data'[ToTT]), TRUE(), __SelectedDate<'Risk Data'[ToTT]))

But I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID

---

I have test on my side, This formula works well, and what is " I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID"?

Regards,

Lin

[Anonymous · Accepted solution]

Ah sorry, I've just realized if I use my count of acting as the visual level filter, I just need to set it to is greater than 0 to get the effect I was looking for.

The previous response about setting the filter to true was confusing me since it wasn't an IF measure.