NONVISUAL
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
What is the NONVISUAL function?
The NONVISUAL function in DAX is used to perform calculations on columns of data without displaying the result in the final output. It is similar to the SUMMARIZE function, but it does not create a new table or add a column to the existing table. Instead, it creates a temporary table that is used in the calculation and then discarded.
The NONVISUAL function is commonly used in complex calculations that involve multiple steps or intermediate results. It helps to simplify the DAX formula by breaking it down into smaller parts and reducing the number of columns in the final output.
Syntax of the NONVISUAL function
The syntax of the NONVISUAL function is as follows:
= CALCULATETABLE ( ❰Expression❱, ❰Filter❱ )
Where:
- Expression: The DAX expression or formula that calculates the value to be used in the calculation.
- Filter: The DAX filter or condition that determines which rows of data to include in the calculation.
The CALCULATETABLE function is used to create the temporary table that is used in the calculation. It evaluates the Expression parameter for each row of data that meets the conditions specified in the Filter parameter.
Examples of using the NONVISUAL function
Let's look at some examples of using the NONVISUAL function in Power BI.
Example 1: Calculate the average sales per month
Suppose we have a sales table that contains the sales data for different products and regions. We want to calculate the average sales per month for each product.
To do this, we can use the following DAX formula:
= AVERAGEX (
CALCULATETABLE (
VALUES ( Sales[Product] ),
ALL ( Sales ),
DATESYTD ( ‘Date'[Date] )
),
[Sales Amount]
)
In this formula, we use the AVERAGEX function to calculate the average sales value for each row of data in the temporary table created by the CALCULATETABLE function. The temporary table includes only the product column, and it is filtered by all rows in the sales table and the year-to-date dates.
Example 2: Calculate the running total of sales
Suppose we have a sales table that contains the sales data for different products and regions. We want to calculate the running total of sales for each product.
To do this, we can use the following DAX formula:
= SUMX (
CALCULATETABLE (
VALUES ( Sales[Product] ),
FILTER (
ALL ( Sales ),
Sales[Date] ❰= MAX ( Sales[Date] )
)
),
[Sales Amount]
)
In this formula, we use the SUMX function to calculate the running total of sales for each row of data in the temporary table created by the CALCULATETABLE function. The temporary table includes only the product column, and it is filtered by all rows in the sales table that have a date less than or equal to the maximum date in the sales table.
The NONVISUAL function in DAX is a powerful tool that allows you to perform complex calculations on columns of data without displaying the result in the final output. It helps to simplify the DAX formula and reduce the number of columns in the final output. By using the examples provided in this article, you can apply the NONVISUAL function to your own Power BI projects and take your data analysis to the next level.
