Forum Discussion
Count if a rows range falls within another range
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 ) )
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]).
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?
That would warrant a separate question, I think.