FILTER is not returning a table - bug with "what-if" parameter?

[marcoselias]

Hello,

I am trying to create a dynamic table based on the value of a measure - a "what-if" parameter value.

The measure is something like:

Threshold Value = SELECTEDVALUE('Thresholds'[Threshold Options])

As you know with "what if" parameters, Threshold Value defined above will have the value that the user enters through the slicer. This works, and I have confirmed in many ways including the use of card and table visuals. Furthermore, the following returns a correct count and is updated correctly every time I change the value of the what-if parameter:

CountOfOnOrUnderT = COUNTROWS(FILTER('Table1', 'Table1'[Field1] <= [Threshold Value]))

The formula above returns a correct count because of row context - "FILTER is both a table function and an iterator [...] it scans the table evaluating the condition on a row-by-row basis." (The Definitive Guide to DAX 2nd ed., Russo & Ferrari, p.61)

Now, since "The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.", I have tried to get the table defined by my expression (i.e., what's inside COUNTROWS in the formula above), but this doesn't work:

DynamicTable = FILTER('Table1', 'Table1'[Field1] <= [Threshold Value])

Why does COUNTROWS on the table defined by an expression works fine but I cannot "see" this table? What am I missing? If this is a limitation, what is it? Or is this a bug?

Thanks in advance!

[PaulDBrown · Accepted solution]

Calculated tables are not dynamic (unless they are virtual tables as part of a measure). In other words, they are loaded when the model is loaded. They are unaffected by slicers etc...

When you use a measure, the calculation returns a scalar value. This value will depend on row and filter context.

In your case you are trying to create a (¨physical¨) table referenced to a SELECTEDVALUE measure.

DynamicTable = FILTER('Table1', 'Table1'[Field1] <= [Threshold Value])

A calculated table which is not part of a measure will ignore this. You can actually test this by hard coding the parameter, so instead of [Theshold Value] type in a relevant number.

The solution is however simple: use the [CountOfOnOrUnderT] measure as a filter for a table/matrix/visual in the filter pane.

[nvprasad]

Hi marcoselias,

Could you please try with the below dax ?

DynamicTable = FILTER('Table1', 'Table1'[Field1] <= SELECTEDVALUE('Thresholds'[Threshold Options]))

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad