# DAX functions I liked

So as I am developing DAX formulas for PowerPivot, I got to learn really nice functions that I found interesting to share:

Divide by Zero Solution:
DIVIDE
: DIVIDE(Nomenator, Denametaro, alternativeValue).

Now you have the divide-by-zero handling really easy with one single parameter “alternativeValue” showing what to show when you are in such situation. And even if this parameter isn’t filled with value, the measure won’t show an error, rather a blank.

Running Totals:
I found this one very interesting if you ever needed it. Having a Running totals function in dimensional model saves you much effort and time of intra-cross-joining in fact table in relational model (database), and lets you avoid one reason for materializing views, which are normally ETL/report-rendering time consuming and limit your ability to drill-down/roll-up by different dimensions.

Here is a nice example for running totals.

In my application, I attempted synthesizing how customers funnel into different billing cycles just by simply using the cycle they are at currently. Basically that enabled me to just use the order table and thus avoid drowning into the gigantic transaction table.

OrderByCycle :=
CALCULATE (  ‘Order'[CustomersCount],
FILTER (
ALL ( ‘Order Item'[LatestCycle] ),
‘Order'[LatestCycle] > MAX ( ‘Order'[LatestCycle] )
)
)    + ‘Order Item'[CustomersCount]

This function returns the username of the user running the report. The format is: [domain][username]. You cannot use it in a calculated field but as a measure. Go to the measures area and write: ThisUser:=USERNAME(). Then go to your report and drop this measure to your pivot table. It will show the current user. This can be useful for filter data by user, or applying a row-level security.
Here are further examples of using it. Here is a nice example to implement this security in PowerPivot.

LOOKUPVALUE:

Can be used to select a subset of values of a field, based on filters. A great application of this is the row-level security. Here is an example that you can use in a role filter of an SSAS Tabular model role configuration: