Flag inbetween rows based on another table's Start & End info

[Austinger]

Hi My mighty friends

Simplified model: I have a list like this:

ID	Start M	End M
SR-001	3	6
SR-002	8	9

and another list from 0-10

how can I achieve this result? These SR-001, SR-002, etc. never overlap with each other.

M	ID
1
2
3	SR-001
4	SR-001
5	SR-001
6	SR-001
7
8	SR-002
9	SR-002
10

[TheoC · Accepted solution]

Hi @Austinger 

You can do this by creating the following calculated column in your List table (the one with 0 to 10 in it):

_calcol = 

VAR _1 = 

     CALCULATE ( 
         VALUES ( 'Table'[ID] ) ,
            FILTER ( 
                'Table' , 'Table'[Start M] <= EARLIER ( 'Table (2)'[Value] ) && 'Table'[End M] >= EARLIER ( 'Table (2)'[Value] ) ) )
RETURN

_1

Output will look like below:

Hope this helps 🙂

Theo

[Austinger]

Great, exactly what I want. Thank you so much guru.

Sad thing is even with the codes provided, I still can't logically understand how it worked. Need to learn more...

[TheoC]

Hi @Austinger 

I hope the below provides some context:

VALUES: https://docs.microsoft.com/en-us/dax/values-function-dax

FILTER: https://docs.microsoft.com/en-us/dax/filter-functions-dax

EARLIER: https://docs.microsoft.com/en-us/dax/earlier-function-dax

All the best and feel free to touch base with any additional questions k!

Theo 🙂

[Austinger]

Really appreciate, guru.

That's very helpful. Thanks again for guiding me.

And with your help, finally delivered exceed expectation, visualize the impact to the network. 

[TheoC]

@Austinger based on the screenshot, it looks like you are the guru, not me!  Well done my friend. That looks awesome!

[TheoC]

Hi @Austinger 

On phone atm but will provide you with an explanation tomorrow for context!

Theo 🙂