Forum Discussion
Grouping with gaps
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 |
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.
2 Replies
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.
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
