Grouping with gaps

[JCG99]

Hello,
I am trying to group data with gaps inside them. This is a sample of my data:

distance from	distance to	built year	Type road	Living area	Zone	State
0	10	1990	Alley	Yes	A	Good
10	15	1990	Alley	Yes	A	Good
20	25	1995	Alley	Yes	A	Good
30	60	1995	Street		A	Bad
60	71	1990			A	Bad
100	120	2020			A
300	370	1995	Highway	No	B
370	400	2020	Highway	No	B

I want to group my values by 'distnace from' and 'distance to' in a table. I have defined field paramters that the user would choose to display on the table:

The problem is that it doesn't not diaply the right data when there are gaps. Take for instance the case when I display the built year.

This is what I get by using min/max for the distance from and distance to (renamed measure_from/measure_to, as it is now a measure):

 Measure_fromMeasure_tobuilt year 

0	71	1990
20	370	1995
100	400	2020

And I should be getting this:

Measure_fromMeasure_tobuilt year 

0	15	1990
20	25	1995
30	60	1995
60	71	1990
100	120	2020
300	370	1995
370	400	2020

[JCG99]

Thanks for the answer. The grouping works good when the column buitl year and type road are selected, but not in other configuration. 

For instance, when only built year is selected, I get this result: 

However, I expect to get all type of road:

0 -> 25 Alley

30 -> 60 Street 

60 -> 120 /

300 -> 400 Highway

[v-jialongy-msft · Accepted solution]

Hi @JCG99 

Please try the following DAX:

Min from = 
VAR _selectyear =  SELECTEDVALUE('Table'[built year ])
VAR _selecttype = SELECTEDVALUE('Table'[Type road ])
RETURN
MINX(FILTER(ALL('Table'),'Table'[built year ] = _selectyear && 'Table'[Type road ]=_selecttype),'Table'[distance from])

Max to = 
VAR _selectyear =  SELECTEDVALUE('Table'[built year ])
VAR _selecttype = SELECTEDVALUE('Table'[Type road ])
RETURN
MAXX(FILTER(ALL('Table'),'Table'[built year ] = _selectyear && 'Table'[Type road ]=_selecttype),'Table'[distance to ])

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.