topic Re: Grouping with gaps in DAX Commands and Tips

Grouping with gaps

JCG99 — Thu, 20 Jun 2024 08:11:55 GMT

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

Re: Grouping with gaps

Anonymous — Fri, 21 Jun 2024 01:40:59 GMT

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.

Re: Grouping with gaps

JCG99 — Thu, 27 Jun 2024 08:59:47 GMT

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