topic Re: Use RANK(X) to Filter Values in DAX Commands and Tips

Use RANK(X) to Filter Values

dknipfer — Fri, 27 Sep 2024 04:37:06 GMT

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

Re: Use RANK(X) to Filter Values

OwenAuger — Fri, 27 Sep 2024 06:14:29 GMT

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?

Re: Use RANK(X) to Filter Values

dknipfer — Fri, 27 Sep 2024 14:18:45 GMT

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.

Re: Use RANK(X) to Filter Values

dknipfer — Fri, 27 Sep 2024 18:25:46 GMT

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.

Re: Use RANK(X) to Filter Values

OwenAuger — Sat, 28 Sep 2024 20:31:03 GMT

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?