Data Warehousing, BI and Data Science

4 November 2018

A few DAX functions

Filed under: Data Warehousing — Vincent Rainardi @ 2:34 pm

This query shows summarise, addcolumns, filter, union, row and format DAX functions:

UNION
( ADDCOLUMNS
  ( SUMMARIZE
    ( FILTER
      ( financials,
        financials[Product] = "Montana"
      ),
      financials[Country]
    ),
    "Sales", FORMAT(CALCULATE(SUM(financials[ Sales])), "#,###")
  ),
  ROW("Total", "Total", "Sales", FORMAT(SUM(financials[ Sales]), "#,###"))
)
  • The financials table is filtered on Product = Montana
  • Display just the Country column using Summarize
  • Sales column is added using AddColumns functions
  • Total row is added using Union function
  • Add commas for thousand separator using Format function

Output:
Canada 24,887,655
Germany 23,505,341
France 24,354,172
Mexico 20,949,352
USA 25,029,830
Total 118,726,350

Data source: link
Ref: Whytheq: link, SQLBI: link

Advertisements

1 Comment »

  1. Reblogged this on MS Excel | Power Pivot | DAX | SSIS |SQL.

    Comment by selfservicebi — 4 November 2018 @ 5:27 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: