UPDATE 4/24/2019 The new JSON-based Tabular schema doesn't support extensions so Tabular Editor and BI Developer Extensions (BIDS) won't work. In my previous blog, I announced a few new features for DAX Editor for Tabular. Today, I checked in another change that adds support for default members. Currently, Tabular doesn't have UI for defining default members. However, you can define default members in the MdxScript…
Read article
When you work on more complicated DAX measures, you should get out of the Power Pivot or BISM design environment and use the excellent DAXStudio (or SSMS) with the EVALUATE query syntax. But then eventually the measure would return a scalar value while EVALUATE requires a table. You can use the DAX ROW function to create a single-row, single-column table, and then show the result…
Read articleSometimes, your DAX calculated measures might need to filter a table dynamically based on a certain condition. For example, you might have a Type 2 table like this one: ClaimID ClaimDate ClaimStatus RowStartDate RowEndDate XXX-1 1/1/2015 Open 1/1/2015 1/15/2015 XXX-1 1/1/2015 Approved 1/16/2015 12/31/9999 When a change is detected on the existing claim, this design expires the previous row and adds a new one. A…
Read article
Variance calculations are a common BI requirement but good reference material is lacking. How do we create them in DAX? Let's say you need to calculate the variance between this month sales and the previous month sales. DAX date functions require a Date table. They won't work if you don't have a separate Date table. They also require that you tell PowerPivot (or Tabular) about…
Read articleThis is a big topic and frankly it's too ambitious on my part to tackle it. Assuming equivalent multidimensional (BISM Multidimensional) and tabular (BISM Tabular) models, I was curious how a multidimensional cube fares against VertiPaq in terms of performance. To be fair to VertiPaq, I decided to use native DAX queries. As you've probably heard, BISM Tabular in SQL Denali will include a variant…
Read article
