Forum Discussion
Filtering Consecutive Dates by a Measure
hello AMONT
Glad it is worked as intended.
if you need to limit certain id, you can add more condition inside the filter.
for example:
MAXX(
FILTER(
ALL('Table'),
'Table'[DateOnly]<_Select&&
'Table'[Well_ID]=_ID *** adding at top var _ID = SELECTEDVALUE('Table'[Well_ID])***
),
'Table'[DateOnly]
)or directly use should have same result :
MAXX(
FILTER(
ALL('Table'),
'Table'[DateOnly]<_Select&&
'Table'[Well_ID]=SELECTEDVALUE('Table'[Well_ID])
),
'Table'[DateOnly]
)This way, the measure only calculate for previous date AND for same ID.
you can add more filter condition as you need as above.
Hope this will help.
Thank you.
Hi, thanks for taking a stab at this!
For the 10/20 vs 10/24, that was me, just a typo, your values are correct.
I went the DAX route first, and I get an error for a circular dependency on the first step. In my dataset, the Total Days With 3 Exceed Ref is a measure, and I think that's what causing it. That measure is the product of three previous measures, which is why I think this has become such a headache for me:
The following measures are what has resulted in Total Days with 3 Exceed Ref:
The first measure summarizes the column Exceeds Ref, which can have values ranging from 0 to 36, down to 0 to 3, as I have the hours filtered in my dataset.
hello AMONT
here is the tweak if [Total Days With 3 Exceed Ref] as a measure.
you can continue this below step from creating measure for [Total Days With 3 Exceed Ref].
Right side of above screenshot is using [Total Days With 3 Exceed Ref] as a measure which has exact same result as using calculated column in left side.
As measure works in filter context, so you need to have 'DateOnly' column as filter or else you will have blank because there is no filter.
1. create a new measure for calculating end of conscutive date
Measure Changed Value =
var _Select = SELECTEDVALUE('Table'[DateOnly])
var _Date =
MAXX(
FILTER(
ALL('Table'),
'Table'[DateOnly]<_Select
),
'Table'[DateOnly]
)
var _Value =
MAXX(
FILTER(
ALL('Table'),
'Table'[DateOnly]=_Date
),
[Measure Total Days With 3 Exceed Ref]
)
Return
IF(
[Measure Total Days With 3 Exceed Ref]<>_Value,
1,
0
)
Measure Min DateOnly =
MINX(
FILTER(
ALL('Table'),
'Table'[DateOnly]>=SELECTEDVALUE('Table'[DateOnly])&&
[Measure Changed Value]=1&&
[Measure Total Days With 3 Exceed Ref]=1
),
'Table'[DateOnly]
)
Measure Remove Duplicate =
IF(
[Measure Min DateOnly]=SELECTEDVALUE('Table'[DateOnly]),
1,
0
)
5. create a new measure for calculating number of consecutive date.
Measure Count =
DATEDIFF(
[Measure Min DateOnly],
[Measure Next Date],
DAY
)
First off, I appreciate all the time you've put into this. I owe you big time! I know this is so close to correct, but I have no idea what I'm doing wrong.
The [Measure Total Days With 3 Exceed Ref] doesn't work in my pbix. I cannot perform the SUM measure since it's not a column in the table, just a measure based on several other measures as described above. As such, I get a weird result in all the other formulas that you provided.
I think I need a measure to mimic your measure, but in the summation of the [Total Days With 3 Exceed Ref] measure that I have. In the Card that I added, it defaults to the sum, so I guess I need that in a measure to make the same result?
One thing to make sure is clear, this is a single well (Well 100) example. I have over 100 wells. So I am assuming I need the measure to be filtered by well number.
hello AMONT
i think if [measure total days with exceed]is a measure, then you should have no issue.
The SUM in [measure total days with exceed] in my pbix is meant to make [measure total days with exceed] behaves as measure.
You can do your [measure total days with exceed] measure to continue your calculation.
If you still have wrong result perhaps you have another calculation that affect your final desired outcome.
Please share your pbix contained of a sample of your original data so we can work it out to achive your desired outcome.
Please remove any confidential information.
Thank you.Hello,
I began to pull a subset of the data with the Well 100 example, and once that data was on it's own, without any other wells, the measures and everything worked just like your screenshots!
So it will definitely work, but I think I need things filtered by the [Well_Number] field that I have, as all the wells can have the same dates. In that case, with the measures that you provided, would I just update the filters in some way to make the measures calculate not only by DateOnly, but also by Well_Number?
I can pull a data subset with 2-3 wells if that would help.