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]

USERNAME():

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:
=’Company'[Name]=LOOKUPVALUE(‘UserPermissions'[Company], ‘UserPermissions'[username], USERNAME(), ‘UserPermissions'[Company], ‘Compay'[Name])
You will need first to create and populate the UserPermissions, basically the relationship between the username and the row filter. In this example: the row filter is the Company this user belongs to. Here is a more complete example of it.

TOTALMTD:
Calculates a measure against the period of the beginning of the month up to today. So ever if we filter by one day, this function breaks this filter and bring all days from day 1 in today’s month, up to today.
TOTALMTD(<Scalar value expression>,<dates>[,<filter>])
Example: MTDMeasure = TOTALMTD(SUM(),Date[DateField])
Very useful for comparing today’s performance vs this month’s average performance.
For ratio scalar measures, one can compare directly against the function. But for aggregated (SUM, COUNT) measures, one needs to divide by the days the passed in the month.
So: DIVIDE( TOTALMTD(SUM(),Date[DateField]), DAY(Date[DateField]), 0)

Leave a Reply

Your email address will not be published. Required fields are marked *