Finding parallel time slots for 2 out of 3 events

[patrickmaul]

Hi everybody,

I have a quite complex DAX and modelling problem. Basically, I have assets and asset halts. Each asset halt has a start and end datetime and I need to find out how long each asset was halted in a selected date period. However, there are some specific assets where my customer wants to know if they were halted at the same time. And one special challenge is to find out if exactly 2 out of 3 assets halted at the same time.

These assets are fixed and can be hard coded inside the DAX. Customer does not need them in a separate table, although it would be okay if it was easier.

For simple halts, my measure works fine, although maybe could be a little bit more efficient.

Asset halt (h) =
VAR _minDate =
    MIN ( 'Dim Date'[Date] ) //+1 to get 0:00 AM on the next day
VAR _maxDate =
    MAX ( 'Dim Date'[Date] ) + 1
RETURN
    CALCULATE (
        SUMX (
            'FACT Asset Halts',
            VAR _min =
                MAX ( _minDate, 'FACT Asset Halts'[Start DT] )
            VAR _max =
                MIN ( _maxDate, 'FACT Asset Halts'[End DT] )
            RETURN
                MAX ( 0, DATEDIFF ( _min, _max, MINUTE ) )
        )
    )

For 2 out of 2 simultaneous halts, I have solved it by creating an SUMX iterator nested inside the other SUMX. This works, however as you can imagine, it is not very fast.

Asset halt (h) =
VAR _minDate =
    MIN ( 'Dim Date'[Date] ) //+1 to get 0:00 AM on the next day
VAR _maxDate =
    MAX ( 'Dim Date'[Date] ) + 1
VAR _assetID1 = "A1"
VAR _assetID2 = "A2" //Prefilter my fact table for performance
VAR _filteredFactA1 =
    CALCULATETABLE (
        'FACT Asset Halts',
        FILTER ( 'DIM Assets', 'DIM Assets'[UID] = _assetID1 ),
        FILTER (
            'FACT Asset Halts',
            'FACT Asset Halts'[Start DT] < _maxDate
                && 'FACT Asset Halts'[End DT] > _minDate
        )
    )
VAR _filteredFactA2 =
    CALCULATETABLE (
        'FACT Asset Halts',
        FILTER ( 'DIM Assets', 'DIM Assets'[UID] = _assetID2 ),
        FILTER (
            'FACT Asset Halts',
            'FACT Asset Halts'[Start DT] < _maxDate
                && 'FACT Asset Halts'[End DT] > _minDate
        )
    )
RETURN
    CALCULATE (
        SUMX (
            'FACT Asset Halts',
            VAR _min =
                MAX ( _minDate, 'FACT Asset Halts'[Start DT] )
            VAR _max =
                MIN ( _maxDate, 'FACT Asset Halts'[End DT] )
            RETURN
// nested SUMX, try to find if there are any halts on the second asset occuring during the first halt
                SUMX (
                    _filteredFactA2,
                    VAR _min2 =
                        MAX ( _min, [Start DT] )
                    VAR _max2 =
                        MIN ( _max, [End DT] )
                    RETURN
                        DATEDIFF ( _min2, _max2, MINUTE )
                )
        )
    )

But for 2 out of 3, this solution breaks. I tried nesting another SUMX, but it is just too slow to work with. Anybody got any other ideas. Currently, I have a star schema and would like to keep it that way. However, if you have any ideas that maybe involve branching to a snowflake.

Thank you in advance.

[OwenAuger · Accepted solution]

Hi @patrickmaul

This is an interesting one 🙂

From what I understand of your current model, for these sorts of calculations, the complexity does grows considerably as more assets need to be considered.

My suggestion would be, if you are happy to work with discrete minutes, restructuring your Fact Asset Halts table so that it contains one row per Asset/Minute when a halt occurs. This will increase the row count to the total number of asset/minutes that halts occurred, which could be large, but shouldn't be a problem for Power BI as the cardinality of each column would still be quite low. The benefit is that this should greatly simplify the DAX expressions and improve performance by avoiding the issues with nested iterations using SUMX.

With this method, we can measure how many asset/minutes of halts have occurred by counting rows in the fact table, and we can count how many assets halted during a given minute by summarizing by Date/Minute and counting assets.

I have created an example PBIX showing how I would approach this.

Note:

• There are likely some details to check when converting to discrete minutes, such as off-by-one issues when determining the start and end minutes for a particular halt. Some changes might be needed to how the discrete minutes are derived from Start DT and End DT.
• I couldn't reconcile my 2 out of 2 measure exactly against your original version. There appears to be an issue in the original measure's logic with the nested SUMXs that resulted in results < 0 or > 1,440 on a single date, but I haven't yet figured it out. 

Here are the steps I would suggest:

1. Transform Fact Asset Halts from this

into this (extract shown):

The Halt Time Key is the daily minute index running from 0 to 1,439. This is related to a Dim Time dimension in my sample model.

2. Model is then structured like this:

3. Then create measures like this:

Asset halt (h) = 
COUNTROWS ( 'FACT Asset Halts' )

Asset halt (h) 2 out of 2 =
-- Return number of minutes for which A1 & A2 both halted.
VAR Assets = { "A1", "A2" }
VAR AssetCountRequired = 2
VAR DateMinuteAsset =
    CALCULATETABLE (
        SUMMARIZE ( 'FACT Asset Halts', 'Dim Date'[Date], 'Dim Time'[Time Key], 'DIM Assets'[UID] ),
        TREATAS ( Assets, 'DIM Assets'[UID] )
    )
VAR DateMinuteAssetCount =
    GROUPBY ( DateMinuteAsset, 'Dim Date'[Date], 'Dim Time'[Time Key], "@NumAssets", SUMX ( CURRENTGROUP ( ), 1 ) )
VAR Result = COUNTROWS ( FILTER ( DateMinuteAssetCount, [@NumAssets] = AssetCountRequired ) )
RETURN
    Result

Asset halt (h) 2 out of 3 = 
-- Return number of minutes for which exactly 2 of A1, A2, & A3 were halted.
VAR Assets = { "A1", "A2", "A3" }
VAR AssetCountRequired = 2
VAR DateMinuteAsset =
    CALCULATETABLE (
        SUMMARIZE ( 'FACT Asset Halts', 'Dim Date'[Date], 'Dim Time'[Time Key], 'DIM Assets'[UID] ),
        TREATAS ( Assets, 'DIM Assets'[UID] )
    )
VAR DateMinuteAssetCount =
    GROUPBY ( DateMinuteAsset, 'Dim Date'[Date], 'Dim Time'[Time Key], "@NumAssets", SUMX ( CURRENTGROUP ( ), 1 ) )
VAR Result = COUNTROWS ( FILTER ( DateMinuteAssetCount, [@NumAssets] = AssetCountRequired ) )
RETURN
    Result

 For the 2 of 2 & 2 of 3 measures, I have followed a similar pattern to that used in this SQLBI article.

Sample outputs

There was an issue in my copy of the original 2 out of 2 measure which I haven't figured out. However, the new 2 out of 2 measure seems to give correct results.

Does this method seem workable in your model?

It might be useful if you could post some sample data or a PBIX with expected results to confirm this works.

Regards,

Owen 🙂

[patrickmaul]

Thanks once again. The solution is great, and works really good for small datasets. I probably have to tweak it a little bit, because it would produce too many lines, but I can do this myself in accordance with my customer's needs.

[patrickmaul]

Wow, this already looks great - I have to try it tomorrow (German Time) in the morning.