Count if a rows range falls within another range

[larrylob]

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!

[AlexisOlson · Accepted solution]

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
        )
    )

[larrylob]

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!

[AlexisOlson]

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]).

[larrylob]

Thanks! It worked. I'm wondering now is there a way to link the column to the data so that when you show the data as a table you can pull the actual information from the source that the column is counting? 

[AlexisOlson]

That would warrant a separate question, I think.