Calculating the number of latest Checks

[BitKing123]

I have a datamodel, modelled as a star schema with facts in the middle and dimensions around it. All dimensions are 1 and the facts are on the many side.
The data consists of Checks done on equipment at locations worldwide.

Now, I need a DAX measure to show me the Number of Recent checks. "Recent" in this case, is the last check per combination of [Location]/[EquipmentCategory], these fields are in different dimension tables.

I have an [Index] field in the DimChecks table, that is sorted in a way that a 1 always shows the latest check per [Location]/[EquipmentCategory]. This is modelled upstream in the data flow and outside of PBI/DAX.

So, When nothing is selected in slicers, the desired measure is simple: All Checks where IndexField=1. Simply:

LatestReviews = 
 CALCULATE(
    DISTINCTCOUNT(Facts[CheckID]),  
    DimChecks[Check Sort Index] =1 
    )

However, If the user than slices for example, the year 2023, I would like it to show the number of "lastest checks" in that year.
And, for some combinations of [Location]/[EquipmentCategory] the lowest index ín the year 2023, might be 2, if the last check was in 2024.
Obviously, in the above DAX only Checks with [Check Sort Index]=1 are shown.

I think I need something that first aggregates the data and calculates the lowest index number per [Location]/[EquipmentCategory], and that is still affected by all slicers / selections (Year above is an example, there are also other applicable slicers) and then something that counts or sums the values in that table. But I cant seem to get the right formula...

I tried the following, but when sliced to 2023, it only gives me the number of checks where index=1...

LatestChecks = 
VAR MinCheck= 
    CALCULATE(
MIN(DimChecks[Check Sort Index]),
ALLEXCEPT(Facts, DimChecks[EquipmentCategory],DimLocations[LocationName])
)

RETURN

CALCULATE(
    DISTINCTCOUNT(Facts[CheckID]), 
    FILTER(
        DimChecks, 
        DimChecks[Check Sort Index]=MinCheck
        
        ))

Hope y'all can help!

[OwenAuger · Accepted solution]

Hi @BitKing123 

It sounds like the INDEX function would work well for this.

INDEX can be used in this case to return the combinations of EquipmentCategory/LocationName/Check Sort Index corresponding to the minimum Check Sort Index, partitioned by EquipmentCategory/LocationName.

For example:

LatestChecks =
VAR MinCheckCombinations =
    INDEX (
        1,
        SUMMARIZE (
            Facts,
            DimChecks[EquipmentCategory],
            DimLocations[LocationName],
            DimChecks[Check Sort Index]
        ),
        ORDERBY ( DimChecks[Check Sort Index], ASC ),
        KEEP,
        PARTITIONBY (
            DimChecks[EquipmentCategory],
            DimLocations[LocationName]
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Facts[CheckID] ),
        MinCheckCombinations
    )

Does this work as intended?

Regards

[OwenAuger · Accepted solution]

Hi @BitKing123 

It sounds like the INDEX function would work well for this.

INDEX can be used in this case to return the combinations of EquipmentCategory/LocationName/Check Sort Index corresponding to the minimum Check Sort Index, partitioned by EquipmentCategory/LocationName.

For example:

LatestChecks =
VAR MinCheckCombinations =
    INDEX (
        1,
        SUMMARIZE (
            Facts,
            DimChecks[EquipmentCategory],
            DimLocations[LocationName],
            DimChecks[Check Sort Index]
        ),
        ORDERBY ( DimChecks[Check Sort Index], ASC ),
        KEEP,
        PARTITIONBY (
            DimChecks[EquipmentCategory],
            DimLocations[LocationName]
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Facts[CheckID] ),
        MinCheckCombinations
    )

Does this work as intended?

Regards

[BitKing123]

@OwenAugerthank you! This is a very interesting approach! In all my Googles, I did not stumble upon this approach 👍

One question on the code: Could you clarify what you intend with KEEP(). DAX does not recognize it (PBI Desktop 2024-04) and I do not think a KEEPFILTERS makes sense here? 

It seems to work as intended without KEEP, but I am still curious to know what your intention was here.

[OwenAuger · Accepted solution]

You're welcome @BitKing123 

INDEX is one of the relatively new "window functions" introduced in December 2022.

Regarding KEEP:

• My apologies, DEFAULT is the expected value for this argument now, not KEEP.
• This 4th argument of INDEX is an optional argument specifying how blanks are to be handled.
• Originally the default (and only allowed) value which could be optionally provided was KEEP, but it has since been updated to DEFAULT (see here).
• However both options for this argument still work and produce the same result, but DEFAULT is underlined in red as an "error" in Power BI Desktop.
• You can omit this argument completely and produce the same result.
• Sorry for the confusion!

An alternative (and still perfectly valid) way to write the above measure that I would have suggested before the window functions were released :

LatestChecks =
VAR MinCheckCombinations =
    GENERATE (
        SUMMARIZE (
            Facts,
            DimChecks[EquipmentCategory],
            DimLocations[LocationName]
        ),
        CALCULATETABLE (
            FIRSTNONBLANK ( SUMMARIZE ( Sales, DimChecks[Check Sort Index] ), 0 )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Facts[CheckID] ),
        MinCheckCombinations
    )

Regards

[BitKing123]

Clear, thank you for the addition explanation!
This alternative approach works as well, same results.