Forum Discussion
Count if a rows range falls within another range
Hello,
I have looked around and have not found an answer to my problem and hope someone could help me.
Right now i have a few different tables from different entities that have constraints that fall within a certain mile marker range.
| Constraints | |||
| Mile Start | Mile End | Constraint | Resolved |
| 3.1 | 5.2 | Trees | Yes |
| 1 | 9 | Pipe | Yes |
| 7.1 | 8.6 | Pipe | No |
| 12 | 15 | Wall | No |
| 1.21 | 15 | Pipe | No |
| 13 | 15 | Trees | No |
I have a table that separates these mile markers in to specific ranges with a dedicated section name.
| Sections | ||
| Section Name | Mile Start | Mile End |
| A | 1 | 3.5 |
| B | 3.5 | 10 |
| C | 10 | 14 |
I want to be able to count the number of constraints whose range falls within my specified ranges. They can either be fully or partially in the Section to be counted and get something like below
| Final Table | |||
| Section Name | Mile Start | Mile End | # Constraints |
| A | 1 | 3.5 | 3 |
| B | 3.5 | 10 | 4 |
| C | 10 | 14 | 3 |
I could figure this our in excel but I am not sure how to do this in power bi considering that the data will be updated often. Any help would be greatly appreciated.
Thanks!
You can add a calculated column to your Sections table like this:
# Constraints = VAR SectionStart = Sections[Mile Start] VAR SectionEnd = Sections[Mile End] RETURN COUNTROWS ( FILTER ( Constraints, Constraints[Mile Start] < SectionEnd && Constraints[Mile End] > SectionStart ) )
5 Replies
You can add a calculated column to your Sections table like this:
# Constraints = VAR SectionStart = Sections[Mile Start] VAR SectionEnd = Sections[Mile End] RETURN COUNTROWS ( FILTER ( Constraints, Constraints[Mile Start] < SectionEnd && Constraints[Mile End] > SectionStart ) )Thanks for the information Alexis. So this formula has to be added as a custom column in my sections data source through Power Query? The formula doesn't seem to work. Is there a way to call the right information in a measurement straight to my table visual? I will have multiple constraint tables that I would like to create a measurement or something similar for each of these different tables. All this information would be summerized in my table visual.
Thanks!
I was not suggesting a custom column in the query editor but rather a calculated column in DAX.
You'd need to adjust it a bit to use it as a measure instead of a calculated column (e.g. use MIN/MAX aggregations on Sections[Mile Start]/Sections[Mile End]).
