Use $Field to get the COUNT() of members in each Field

[#1]

I have this data:

LOAD * INLINE [

Country, Population

Portlandia, 1

Girlana, 2

Festonville, 3

];

LOAD * INLINE [

Country, Square Footage

Portlandia, 100

Girlana, 200

Festonville, 300

Bombast, 300

];

I want to see this table:

$Field	{Count of Distinct Members in that $Field}
Country	4
Population	3
Square Footage	3

I tried using an expression like this:

COUNT([$($Field)])

But it didn't work...

Notice I don't want this:

$Field	COUNT($Field) // COUNT of Tables in which that $Field appears
Country	2
Population	1
Square Footage	1

[#2]

I also tried this:

$(=COUNT([$($Field)]))

I'm not sure if the $($Field) is working how I expect, even this Expression doesn't produce anything:

='$($Field)'

And even if I make my own data, (rather than using System Field ($Field))...

LOAD * INLINE [

Field

Country

Square Footage

Population ];

...it doesn't work how I expect...

='$(Field)'

I can do simple concatenation:

'' & Field

or

'' & $Field

That's fine, but I want to treat each $Field value as if it were a "fld" so I can pass it to COUNT(...)

[swuehl]

You can't change the expression per dimension line (though you can use a pick/match method to pick the right distinct field count per dimension value).

If you are just interested in the field distinct value counts, go to Setting - Document Properties - Tables.

[#4]

Thanks -- I'm also interested in MAX() for each $Field. So it's a general problem. And it's *very theoretical*, I can think other ways around this... But I'm just curious.

As you say, "can't change expression per dimension line" -- I think you're right. At least, if I just use a plain Text Object, whose value is

*Edit*

=COUNT($(vField))

Where vField is

$(=$Field)

Then, *when I click on an (ONLY) specific $Field value*, the Text Object will show me the correct count; i.e. if I click on "Country" then the Text Object shows 4.

My brain must not be working, I thought the exact purpose of Dimensions vs Expression is to allow the Expression to be evaluated in the "context" of *each* Dimension (line)

[sunny_talwar]

This seems to work only if you select the field. May be somebody can expand on the idea:

=SubStringCount(Concat({1}DISTINCT $(='[' & $Field & ']'), '|'), '|') + 1

[#6]

So I think it's easier to do this:

A plain Text Object, whose value is

=COUNT($(vField))

Where vField is

$(=$Field)

Then it works as in your example.

But how to get the COUNT to appear in a Listbox or Table -- for *each* Dimension?

[swuehl · Accepted solution]

Use a pick/match combination.

[#8]

Just as you said -- must use Pick/Match. Quite ugly, but it works! Thanks.         

[#9]

And it's not so terribly ugly, once it "one-level" evaluates the string part, the result is just mapping each $Field to the correct expression.

Pick( Match($Field, 'Country','Population','Square Footage') , Count( DISTINCT [Country]), Count(DISTINCT [Population]), Count(DISTINCT [Square Footage]))

Have you used this technique elsewhere @swuehl?

[swuehl]

You can use similar e.g. when you need aggregation with set analysis that looks at current dimension values.