How do I count the number of rows in a restricted straight table?

[cranium144]

I'm using a slider bar that controls a variable.  I'm using that variable to show only orders that are less than X dollars (sum([Invoice $]).  This works just fine. 

However, when I do a count(DISTINCT [Invoice Number]), it returns the total number of invoices disregarding the restriction.  I want to know how many are less than the slider bar number, not the total number.

My caption expression is:

= 'Showing ' & num(count(DISTINCT [Invoice Number]), '#,##0') & ' Orders Below: ' & num($(vOrderTotal), '$#,##0')

My dimension limits are set to 'show only values that are:'   'less than'    $(vOrderTotal)

It should tell me 52, but shows 70 for the current selections.

How do I make this return the correct 52 instead of 70?  Thanks!

[swuehl]

Try

count({[Invoice Number] = {"=sum([Invoice $])<$(vOrderTotal)"}} DISTINCT [Invoice Number])

[MK_QSL]

Can you please provide sample data or sample application?

[cranium144]

Did not work. Another way to ask the question: “How do I get the number of rows displayed?”

[swuehl]

'Did not work' is not really helpful.

Have a look at

Number of rows in straight/pivot table into a variable?

[cranium144]

Below is the displayed result.  My caption line counts 70 (which is the TOTAL number, not the restricted number).  My caption line is defined as:

= 'Showing ' & num(count(DISTINCT [Invoice Number]), '#,##0') & ' Orders Below: ' & num($(vOrderTotal), '$#,##0')

Invoice Number	Invoice $
325520	$36.00
325526	$37.20
325544	$37.32
325549	$25.20
325551	$30.00
325558	$49.20
325561	$25.20
325563	$30.00
325566	$49.20
325569	$25.20
325575	$24.00
325577	$12.00
325578	$25.20
325636	$36.00
325638	$12.00
325639	$24.00
325646	$42.00

[swuehl · Accepted solution]

And what do you get with this in a text box (note, I corrected a typo in the set expression):

=count({<[Invoice Number] = {"=sum([Invoice $])<$(vOrderTotal)"} >} DISTINCT [Invoice Number])

[cranium144]

Thank you for the input!  Works great!