Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [dax]

The Data Analysis Expressions (DAX) language is a formula language that allows users to define custom calculated columns and measures in PowerPivot and Tabular SSAS models.

The Data Analysis Expressions (DAX) language is a formula language that allows users to define custom calculated columns and measures in PowerPivot and Tabular SSAS models. DAX includes some of the functions that are used in Excel formulas, and additional functions that are designed to work with relational data and perform dynamic aggregation.

The syntax of DAX formulas is very similar to that of Excel formulas, using a combination of functions, operators, and values. Where DAX formulas differ from Excel formulas is that DAX functions work with tables and columns, not ranges, and let you do sophisticated lookups to related values and related tables. With DAX formulas, you can create aggregations that would ordinarily require in-depth knowledge of relational database schemas or OLAP concepts. Moreover, because calculations in DAX formulas utilize the highly optimized in-memory engine, you can rapidly look up and calculate values across very large columns or tables.

Examples

Calculated Measure

Retrieves the sum of sales amount for the Europe region

=SUMX(FILTER(Sales,Region="Europe"),[SalesAmount])

Calculated Column

Retrieves the full name of the related player from the Players table based upon relationships defined in the PowerPivot/Tabular model

=related(Players[FullName])

Resources

16 questions
7
votes
2 answers

Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters

I'm working on a tabular cube in SSAS 2012 SP1 CU4. I have 3 dimensions (Requisition, Requisition Status, Date) and 1 fact (Requisition Counts). My fact table is at the grain of requisitionKEY, RequisitionStatusKEY, SnapshotDateKey. I have…
mmarie
  • 844
  • 7
  • 19
2
votes
2 answers

Counting rows in a table based on subtable result

Sample data: -------------------------------------------------- | Customer | Value | -------------------------------------------------- | A | 5 …
user169728
  • 21
  • 1
1
vote
1 answer

How do I write a Dax statement that returns the current counts whenever I filter for a TEam

Below is my sample table and I'd like to write a Dax statement that returns the current count whenever I filter for Team A or B. Ideally, the current count per team would be on a card in power…
Sam Johnson
  • 21
  • 6
1
vote
2 answers

How to reference column of DAX variable?

I have a DAX variable that contains a table. How can I reference a particular column in that variable? For example, in the below command, the EVALUATE returns an error. But it works if I replace table1 with FactInternetSales (which is the name of…
explorer
  • 11
  • 1
  • 2
1
vote
1 answer

Dax Formula for Calendar

I have been trying to make a new table and want to use the date of the existing data. Can anyone help me understanding what is wrong with this formula? dDate =…
alphabounce_1228
  • 11
  • 2
1
vote
0 answers

Getting MAX of AVERAGE measure (Power BI/DAX)

I am stumped. I am calculating an average of products sold per location. Then, I am using AVERAGEX to calculate that average for each sales associate. But how do I find the MAX of that average and use that as a benchmark for each associate? I want…
Scott Mohler
  • 23
  • 5
1
vote
0 answers

Making a KPI table in SQL or with dax?

I am going to make a KPI table, however I've yet to decided where to make it. In essence its going to show weekly numbers derived from our customer database. *Weeknumber *New users (users with startdate within that week) *Total users (users with…
B.Doe12
  • 55
  • 5
1
vote
0 answers

SSAS 2014, Relation OLAP - Poor performance, high resource usage, what did I do wrong?

Let me start with the background, I was working on a project that needed a back end that was scalable and fast. Having worked quite a bit with SSAS, I thought I'd try SSAS's in memory solution, Relation OLAP. Long story short, it ended very poorly,…
Randall
  • 121
  • 4
1
vote
1 answer

MDX measure to DAX conversion

I needed a little help on working with MDX and DAX. Recently I have just started working with these two terms and am having a little trouble figuring out the differences and possible examples of how they work. The articles I have read online are…
user282190
  • 111
  • 2
1
vote
1 answer

Filtering by many-to-many table

I'm having a hard time to understand how to filter out result of multidimensional expression. This is my database schema. And this is my data. Manufacturer ╔════╦═══════════════════╗ ║ Id ║ Name ║ ╠════╬═══════════════════╣ ║ 1 ║…
OzrenTkalcecKrznaric
  • 250
  • 1
  • 7
0
votes
1 answer

Why does the DAX syntax require the original name for a filtered table?

I am learning DAX and reading Russo & Ferrari's Definitive Guide to Dax (2nd ed). I understand how SUM ( Sales[Quantity] ) is really SUMX ( Sales, Sales[Quantity] ), which iterates over the rows of the Sales table and sums the values in the Quantity…
jonnybolton16
  • 101
  • 3
0
votes
1 answer

DAX Calculate the sum of values for a year with start date and end date

I can't manage in DAX, to calculate for a year, the sum of the values which are in this year, I would like the proportion of each of the values in a year according to its starting date and its ending date. For example, I want to be able to…
Gabriel Valvin
  • 63
  • 8
0
votes
0 answers

Help with the complex DAX measure

I am struggling with a DAX measure. Here is the fact and 2 dimension tables I have: factAction(dimAccountID, dimActionID, Date, ActionQty) dimAction(dimActionID, ActionCode, ActionDescription) dimDate(Date, WorkingDay) The relationships…
Steve
  • 21
  • 3
0
votes
1 answer

Group by in DAX like in SQL

How can I convert this SQL statement to DAX? SQL query and tables: select SUM(Kiekis ) from Info left outer join Prekes on Info.PrekeID = Prekes.PrekeID left outer join PrekiuGrupes on Prekes.PrekiuGrupeID = PrekiuGrupes.PrekiuGrupeID group by…
Kristina Kristina
  • 21
  • 4
0
votes
1 answer

Link DAX OpenQuery Output to SQL Server Temp Table

I want to record measures from a Tabular model, as KPIs, and store the values in a table in SQL Server. I have created a linked server from my SQL Server instance to my SSAS instance. I have written a stored procedure to execute the DAX code via…
DimUser
  • 382
  • 1
  • 2
  • 16
1
2