The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: Finding parallel time slots for 2 out of 3 eve...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Finding parallel time slots for 2 out of 3 events

06-13-2024
12:20 AM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-13-2024
07:58 AM

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 🙂

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-13-2024
07:58 AM

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 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-14-2024
01:23 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-13-2024
09:15 AM

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