DISTINCTCOUNTCOLUMNVISIBLECELLS

[GeertD]

DISTINCTCOUNTCOLUMNVISIBLECELLS: Count the visible unique elements in a ColumnVector, regardless of their frequencies. Works only on a ColumnVector.
This one is not so trivial. IMO "DistinctCount" should be just another function in the toolbox of the SUBTOTAL function.

=LAMBDA(Source,LET(
   IndexFromZero,SEQUENCE(ROWS(Source),,0),
   IsVisible,SUBTOTAL(103,OFFSET(Source,IndexFromZero,0,1)),
   VisibleSourceData,IF(IsVisible,Source,""),
   COUNTA(UNIQUE(VisibleSourceData))-(1-PRODUCT(IsVisible))
))

DXLR's LAMBDA.LET Library_v00.07.xlsb
	A	B	C	D	E	F	G	H	I	J
1	Filtered Table:
2	First Name	Last Name	Score	Country			CountRows:	20
3	Alice	Livingstone	6	Germany			DistinctCount:	14	(First Name)
4	Mikayla	Lindberg	14	Germany			DistinctCountColumnVisibleCells:	4	(First Name)
7	Myla	Stukeby	5	Germany
9	Mikayla	Slater	14	Mexico
11	Alice	Johansen	34	Mexico			DistinctCount:	16	(Score)
12	Karl	Tilton	11	Mexico			DistinctCountColumnVisibleCells:	6	(Score)
21	Karl	Ingoldsby	13	Germany
23
24	Unfiltered Table:
25	First Name	Last Name	Score	Country
26	Alice	Livingstone	6	Germany
27	Mikayla	Lindberg	14	Germany
28	Charles	Hurst	3	Poland
29	London	Edmund	1	Argentina
30	Myla	Stukeby	5	Germany
31	Xzavier	Angus	4	Romania
32	Mikayla	Slater	14	Mexico
33	Alice	Poole	4	Finland
34	Alice	Johansen	34	Mexico
35	Karl	Tilton	11	Mexico
36	Mikayla	Bruno	18	Finland
37	Paxton	Chadsey	3	USA
38	Lamont	Lacy	17	Slovakia
39	Jaylan	Forbes	9	Egypt
40	Paxton	Beckley	15	Brazil
41	Alejandra	Donoso	22	Spain
42	Deonte	Astley	13	Thailand
43	Alfred	Sarisbury	27	Argentina
44	Karl	Ingoldsby	13	Germany
45	Rylan	Hasbrouck	33	Indonesia
46
Sandbox_Mr.Excel

Cell Formulas
Range	Formula
H2	H2	=COUNTA(tblTop[First Name])
H3	H3	=DistinctCount(tblTop[First Name])
H4	H4	=DistinctCountColumnVisibleCells(tblTop[First Name])
H11	H11	=DistinctCount(tblTop[Score])
H12	H12	=DistinctCountColumnVisibleCells(tblTop[Score])

 

[GeertD]

Here's a better formula (note to Old School thinking me: if you have the filter mask, just use it in FILTER):

DistinctCountVisibleCells(ColumnVector)
=LAMBDA(Source,LET(
   RowdexFromZero,SEQUENCE(ROWS(Source),,0),
   IsVisible,SUBTOTAL(103,OFFSET(Source,RowdexFromZero,0,1)),
   COUNTA(UNIQUE(FILTER(Source,IsVisible)))
))
(ColumnVector)

(I dropped the "COLUMN" from the name)

Note: all formulas can be mashed together since there is no argument repeat, but I left it like this for clarity and educational puposes.

 

[mgirvin]

Totally rad, Geert!!!!!Here is non-lambda:
=ROWS(UNIQUE(FILTER(fSales[SalesRep],SUBTOTAL(3,OFFSET(fSales[[#Headers],[SalesRep]],SEQUENCE(ROWS(fSales[SalesRep])),)))))
Same things but less elegant ; )P.S. It is so funny, Mr Excel posted video yesterday and I could not figure out a single cell formula. But this morning during breakfast, it came to me: Boom! So funny how the creative process works.

 

[GeertD]

Totally rad, Geert!!!!!Here is non-lambda:
=ROWS(UNIQUE(FILTER(fSales[SalesRep],SUBTOTAL(3,OFFSET(fSales[[#Headers],[SalesRep]],SEQUENCE(ROWS(fSales[SalesRep])),)))))
Same things but less elegant ; )P.S. It is so funny, Mr Excel posted video yesterday and I could not figure out a single cell formula. But this morning during breakfast, it came to me: Boom! So funny how the creative process works.

Well, Mike, how about that?!
You used the ROWS function in the calculation. That is even beter!
Here's why I find that:

1. The ROWS function is the equivalent to the COUNTROWS function in DAX (and it also counts blank rows!).
   And whenever the similarities between the various environments can be emphasized, I will choose to do so. Helps with the transition between them.
2. And it also counts blank rows! Making your solution more versatile and more robust than mine.

Thanks, I will adapt, or rather: adopt, in this case.

But wait, there's more: there is one thing I don't understand about your solution:

• You replaced the oh so important arguments "0,1" by "," (just a comma):
  • in the Excel grid that works –I verified myself, too–,
  • but inside of the LET function it doesn't – the "0,1" is crucial (as per my earlier experience and as I explained in my video).
• any idea as to why this behavior is different?

 

[mgirvin]

I have no idea why that does not work in LET!?!?!
The "just a comma" in OFFSET columns will default to zero if left empty. The last two arguments in OFFSET assume the dimensions of the first argument if left out.
But I have no idea why it does not work in LET. Anytime the behavior of a function, especially an old standby like OFFSET does not work the same in a new function, that is worrisome... : (

 

[GeertD]

I have no idea why that does not work in LET!?!?!
The "just a comma" in OFFSET columns will default to zero if left empty. The last two arguments in OFFSET assume the dimensions of the first argument if left out.
But I have no idea why it does not work in LET. Anytime the behavior of a function, especially an old standby like OFFSET does not work the same in a new function, that is worrisome... : (

Hold on, the starting position of your ColumnVector is different from mine: You start from the header, and therefore need a RowdexFromOne, whereas I start from the upper-left position in the ColumnVector itself (that's what it takes if you put in an entire array instead of a single cell), and therefore I need a RowdexFromZero.
So, in case of my formula the other arguments cannot default to equal zero or (the first element in the vector of) the first argument.
I could differentiate the starting position from the vector itself, but that would introduce an extra argument to input to the function. I see you did exactly that:

• Your starting position: fSales[[#Headers],[SalesRep]]
• Your Vector: fSales[SalesRep]

I tested this and indeed that way it works in the LAMBDA.LET-environment, as well, with just the comma – so there is nothing to worry about.

But I'm not going to follow that line of thought, because I feel that adding the need for an additional argument (or conversely: adding more computational complexity [another offset?]) just to be able to use that "just a comma"-trick is a step in the wrong direction, in terms of enduser-friendliness / intuitive use of the function. Please don't take offence.
The way I made the function right now, you use it just the same way as you would any other aggregator function on a column (like SUM, etc.).

Conclusion: everything is predictable and works the same way both in the Excel Grid and in a LAMBDA.LET-environment.

 

[mgirvin]

Awesome!!!! Go Team!!!!!!