Use RANK(X) to Filter Values

[dknipfer]

I need a way to figure out how many sites were online given a specific time. I need to include the ability to slice to a different timeframe. I am using RANK to create a measure and determine the latest row to be counted (Grouped by Site, ordered DESC by ReadEffective then DESC RecordedDateTime). I need to Countrows filtering to Status = 1 AND Rank = 1, or another method of counting active Sites. Also needed is the ability to slice the dates. When I set the latest range of the table to 08/28/2024, for example, the Rank would update to show Sites A, B and D as active as of the last ReadEffective dates, 08/16/2024 (A & B) and 8/25/2024 (D). I cannot filter the Card visual using RANK because it is a measure. How do I accomplish this?

Site	Status	ReadEffective	RecordedDateTime	RANK (measure)
A	0	8/30/2024	8/31/24 12:00 AM	1
A	1	8/30/2024	8/31/24 10:00 AM	2
A	1	8/16/2024	8/17/24 10:00 AM	3
A	1	8/2/2024	8/3/24 10:00 AM	4
A	1	7/19/2024	7/20/24 10:00 AM	5
B	1	8/29/2024	8/31/24 12:00 AM	1
B	1	8/16/2024	8/18/24 12:00 AM	2
B	1	8/3/2024	8/5/24 12:00 AM	3
B	1	7/21/2024	7/23/24 12:00 AM	4
C	1	8/25/2024	8/25/24 12:00 AM	1
C	0	8/12/2024	8/12/24 12:00 AM	2
C	1	7/29/2024	7/30/24 12:00 AM	3
C	1	7/28/2024	7/17/24 12:00 AM	4
C	1	7/27/2024	7/4/24 12:00 AM	5
C	1	7/26/2024	6/21/24 12:00 AM	6
D	1	8/30/2024	8/30/24 12:00 AM	1
D	1	8/25/2024	8/30/24 12:00 AM	2
D	1	8/20/2024	8/30/24 12:00 AM	3

[dknipfer]

This worked like a charm! If I run into any issues I may be back, but right now it's doing exactly what I need.

And to your question, yes, C would still be active. I had been trying to show Sites that would change status or EffectiveDates from the original filter. Sorry for the confusion.

[OwenAuger · Accepted solution]

Hi @dknipfer 

One initial question: If you set the latest range to 08/28/2024, would C not also be active, since it is active as of its latest ReadEffectiveDate 08/25/2024?

Putting that to one side, I would recommend this general approach:

1. Set up the model with 'Date' table related to ReadEffective.

Use 'Date' table for all date filtering.

2. Create these measures, using INDEX to identify the "latest" rows

Latest Active Site Count returns the number of Sites that are active as at their "latest" row up to and including the max filtered date.

Active Site Count = 
VAR LatestRows =
    INDEX (
        1,
        SUMMARIZE ( Data, Data[Site], Data[ReadEffective], Data[RecordedDateTime], Data[Status] ),
        ORDERBY ( Data[ReadEffective], DESC, Data[RecordedDateTime], DESC ),
        DEFAULT,
        PARTITIONBY ( Data[Site] )
    )
VAR LatestRowsActive =
    FILTER ( LatestRows, Data[Status] = 1 )
VAR Result = CALCULATE ( DISTINCTCOUNT ( Data[Site] ), LatestRowsActive )
RETURN
    Result

Latest Active Site Count = 
// Active Site Count up to and including the max filtered Date.
CALCULATE (
    [Active Site Count],
    'Date'[Date] <= MAX ( 'Date'[Date] )
)

3. Use Latest Active Site Count in visuals:

Have I captured the logic correctly, and does this work in your actual model?

[dknipfer]

If I were to add another column of detail to this, say Region, how would I change these measures to perform the same task? I'd be looking to create a bar chart and allow for clicking and filtering of the data by Region.

[OwenAuger]

@dknipfer 

If you are grouping/filtering by Region, the measures shouldn't need to change.

The measures would return the latest active site count within a particular region.

Could you post back an example of how you would want the calculation to work in this case?