NONVISUAL
In SUMMARIZECOLUMNS, marks a filter that ALLSELECTED leaves out.
Group: Table manipulation. Compatible with DAX.
Syntax
NONVISUAL ( <filterTable> )| Parameter | Type | Description |
|---|---|---|
filterTable | Table | A filter argument of SUMMARIZECOLUMNS, such as FILTER or TREATAS over a column the query groups by. |
Returns
Table. The filter itself, marked so that ALLSELECTED leaves it out.
Remarks
- In SUMMARIZECOLUMNS a filter marked NONVISUAL still decides which rows appear, and still filters the expressions. Only ALLSELECTED acts as if it were not there.
- A total over ALLSELECTED therefore covers what the filter took away, as the example shows.
- Every column the filter holds must be one SUMMARIZECOLUMNS groups by. A filter on another column, even of the same table, would change the values themselves, which NONVISUAL is there to prevent: the query is an error, as in Power BI (example 2). Inside
ROLLUPADDISSUBTOTAL, the columns of its levels before the filter count as grouped. - Outside SUMMARIZECOLUMNS it returns its table unchanged.
Examples
A filter ALLSELECTED leaves out
Pils is filtered away, but the selected total still includes it. Without NONVISUAL, the same query gives a selected total of 802,137 liters, the three products shown.
EVALUATE
SUMMARIZECOLUMNS (
Products[Product],
NONVISUAL ( FILTER ( ALL ( Products[Product] ), Products[Product] <> "Pils" ) ),
"Liters", [Liters sold],
"Selected total", CALCULATE ( [Liters sold], ALLSELECTED ( Products[Product] ) )
)
ORDER BY Products[Product]| Products[Product] | Liters | Selected total |
|---|---|---|
| Cider | 301074 | 1806463 |
| IPA | 331884 | 1806463 |
| Lettøl | 169179 | 1806463 |
On a column the query does not group by
Grouped by category, a filter on products would change each category's liters.
EVALUATE
SUMMARIZECOLUMNS (
Products[Category],
NONVISUAL ( FILTER ( ALL ( Products[Product] ), Products[Product] <> "Pils" ) ),
"Liters", [Liters sold]
)Result: an error.
Without NONVISUAL the same filter is an ordinary filter argument, and the query answers.