Dashboard list how to get a distinct list from a distinct list with Arcade?

[ThomasColson]

I'm trying to build a list from a feature class that is a lot of polylines, something like this:

• Trail 1, State 1, District 1
• Trail 1, State 1, District 1
• Trail 1, State 1, District 2
• Trail 2, State 2, District 3
• Trail 2, State 2, District 4
• Trail 3, State 3, District 5
• Trail 3, State 4, District 6
• Trail 3, State 5, District 7
• Trail 3, State 6, District 8
• Trail 3, State 7, District 9

What I want the list to show is: 

• Trail 1, 1 State(s), 2 District(s)
• Trail 2, 1 State(s), 2 District(s)
• Trail 3, 5 State(s), 5 District(s)

What I've got so far is: 

var fs = FeatureSetByPortalItem(Portal('https://arcgis.com/'), 'a454cf97c4264cd2b77f27175e01d3ba', 0, ['TRLNAME','STATE','CONG_DIST'], false);

return GroupBy(fs, ['TRLNAME'], 
[{name: 'total_states', expression: 'STATE', statistic: 'COUNT' }, 
 {name: 'total_districts', expression: 'CONG_DIST', statistic: 'Count' }]); 

 Which gives me

Which is giving me unique trails, but very clearly not the unique state, district for each trail. 

How do I narrow down the expression to return unique states,districts for each trail? Not a programmer.....

[JohannesLindner]

Myabe you can do it in one GroupBy (@jcarlson is the master of that), but I couldn't find a way, so I did it iteratively.

var fs = Featureset('{"geometryType":"","fields":[{"name":"TRLNAME","type":"esriFieldTypeString"},{"name":"STATE","type":"esriFieldTypeString"},{"name":"CONG_DIST","type":"esriFieldTypeString"}],"features":[{"attributes":{"TRLNAME":"Trail 1","STATE":"State 1","CONG_DIST":"District 1"}},{"attributes":{"TRLNAME":"Trail 1","STATE":"State 1","CONG_DIST":"District 1"}},{"attributes":{"TRLNAME":"Trail 1","STATE":"State 1","CONG_DIST":"District 2"}},{"attributes":{"TRLNAME":"Trail 2","STATE":"State 2","CONG_DIST":"District 3"}},{"attributes":{"TRLNAME":"Trail 2","STATE":"State 2","CONG_DIST":"District 4"}},{"attributes":{"TRLNAME":"Trail 3","STATE":"State 3","CONG_DIST":"District 5"}},{"attributes":{"TRLNAME":"Trail 3","STATE":"State 4","CONG_DIST":"District 6"}},{"attributes":{"TRLNAME":"Trail 3","STATE":"State 5","CONG_DIST":"District 7"}},{"attributes":{"TRLNAME":"Trail 3","STATE":"State 6","CONG_DIST":"District 8"}},{"attributes":{"TRLNAME":"Trail 3","STATE":"State 7","CONG_DIST":"District 9"}}]}')

var out_fs = {
    geometryType: "esriGeometryPolyline",
    fields: [
        {name: "Trail", type: "esriFieldTypeString"},
        {name: "States", type: "esriFieldTypeInteger"},
        {name: "Districts", type: "esriFieldTypeInteger"},
        ],
        features: []
}

for(var trail in Distinct(fs, ["TRLNAME"])) {
    var name = trail.TRLNAME
    var filtered = Filter(fs, "TRLNAME = @name")
    var att = {
        Trail: name,
        States: Count(Distinct(filtered, ["STATE"])),
        Districts: Count(Distinct(filtered, ["CONG_DIST"])),
    }
    var geometries = []
    for(var f in filtered) { Push(geometries, Geometry(f)) }
    Push(out_fs.features, {geometry: Union(geometries), attributes: att})
}
return Featureset(Text(out_fs))

[jcarlson]

I really thought it might be possible, but I don't think there's a way around it without looping through. Grabbing the distinct values prior to the loop is probably the best approach I can see.

In normal SQL, there's usually a way to use COUNT(DISTINCT some_column), but that doesn't appear to be the case in the Arcade functions. Sometimes a SQL function that isn't advertised in the AGOL docs is still possible to use in Arcade, so I was hopeful.