Stack Overflow
Stack Overflow

Questions tagged [dax]

Expression language used in Microsoft Power Pivot, Analysis Services, and Power BI for performing analytical calculations.

DAX stands for Data Analysis eXpressions, an expression language created by Microsoft for performing custom analytical calculations within the Power Pivot add-in for Excel, Analysis Services, and Power BI.

DAX functions work on a column or table level to make use of the underlying compression technology used in PowerPivot.

Expressions created in DAX can be used in several ways:

  1. In calculated columns, evaluating the function on a row-by-row basis over an entire table.
  2. In measures, which may be manipulated and aggregated differently, depending on context.
  3. To return a tabular resultset when a table expression is evaluated (fills role of a general purpose query language)
  4. To create a calculated table in a Power BI model as part of the modelling process

Useful Links

8483 questions
9
votes
2 answers

Get Local Time on Power BI Service

As you all might already know, TODAY() function returns UTC time when published to Power BI Service. Our requirement is to return the local (EST) date. As a solution, we created a custom measure that adds UTC offset hours for EST in NOW() and…
Pratik Bhavsar
  • 808
  • 8
  • 32
9
votes
6 answers

Power BI Desktop DAX restart running total column

I have a table where every person has a record for every day of the year. I used this function to achieve a running total based on the daily balance column CALCULATE( SUM(Leave[Daily Balance]), FILTER( ALLEXCEPT(Leave, Leave[Employee Id]), …
LynseyC
  • 93
  • 1
  • 5
9
votes
1 answer

Should FILTER be used inside or outside of SUMMARIZE?

I have these two queries: EVALUATE FILTER ( SUMMARIZE ( 'Sales', Products[ProductName], 'Calendar'[CalendarYear], "Total Sales Amount", SUM ( Sales[SalesAmount] ), "Total Cost", SUM (…
whytheq
  • 34,466
  • 65
  • 172
  • 267
9
votes
5 answers

Is there a way to add line breaks in a string of text in Microsoft's DAX language?

I have a DAX function that pulls in multiple strings of text (from multiple columns) into one cell. But on display I want to have a line break in between the header and the body of the paragraph. Is there a way to code in a line break with DAX? FYI,…
jschlereth
  • 1,071
  • 2
  • 10
  • 12
8
votes
2 answers

Show 0 value on Line Chart in Power BI for missing data

I have a table with sparse values, but in my line chart I'd like the line to go back to zero when there's no data to show. e.g: instead of: I want: To produce the first graph I used a generated table with a series of values for the x-axis, and…
Dan Rayson
  • 1,315
  • 1
  • 14
  • 37
8
votes
3 answers

Measure in DAX to calculate YTD for chosen month only for Power BI

How to construct DAX measure to calculate sum of YTD value for specific month? Here we have FactTable grouped by months. FactTable is filled with both Actual data and Forecast data. The only way to know when Actual end is information in table [Cut…
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
8
votes
1 answer

DAX Equivalent to T-SQL AVG OVER(PARTITION BY)

Apologies if this is a simple thing to achieve but after reading several similar posts, I cannot seem to find the right answer. What I am basically trying to do is replicate the functionality of calculating an average over a group of records. Below…
Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
8
votes
1 answer

Measure that counts entries that contain a specific string

I am making a report in which I have to show the take-rates of different items. Each item exists of a three letter string, and all items are pooled in one and the same column as a large string, separated by spaces. I am now trying to create a…
Dries Weytjens
  • 113
  • 1
  • 1
  • 6
7
votes
1 answer

Limiting the number of retrieved rows using Fill in ADOMD

The following C# code runs a DAX statement and retrieves a DataTable. This works fine, but now I need to retrieve from the database up to N rows. Is there a way to limit the number of rows returned by the Fill function? If not, how can I retrieve…
ps0604
  • 1,227
  • 23
  • 133
  • 330
7
votes
1 answer

Average of Grouped Sum

I am looking to create a measure that takes the average of several sums, grouped by an ID. An example would be if you had a database of customers that each purchased a variety of things, and you wanted to find the average amount that customers spent…
Drew Major
  • 501
  • 1
  • 3
  • 18
7
votes
1 answer

SUM IF in Power BI

Is it possible to SUM on conditions in Power BI? I have a column that contains the values UK and Italy alongside other columns: employee and hours spent. I want to make a measure to show total hours spent by an employee in Italy and another to show…
Shoaib Maroof
  • 369
  • 1
  • 3
  • 13
7
votes
1 answer

How to create a switch that toggles which relationship to use?

I want to create switch button that will be changing date (By Accounting date or by Effective date) by which report is viewed. Currently, I have active relationship by Effective date and inactive relationship by Accounting date. Using…
Serdia
  • 4,242
  • 22
  • 86
  • 159
7
votes
1 answer

Creating a Measure that returns DISTINCT COUNT by Country and Brand in Power BI

I have a table like below: Country, Advertiser, Brand, Spend C1, A1, B1, 10 C1, A1, B2, 5 C1, A2, B3, 0 C1, A2, B4, 20 C2, A1, B1, 8 C2, A1, B5, 7 C2, …
user1330974
  • 2,500
  • 5
  • 32
  • 60
7
votes
1 answer

DAX FILTER function with multiple criteria

I have a DAX statement and I run it inside SSMS. my original statement is: evaluate(filter('rptLoan', [RemainingDays] <= 10)) and it works file. I want to add another criteria as below evaluate(filter('rptLoan', [RemainingDays] <= 10 and [CloseDt]…
user7577311
7
votes
1 answer

How to use GROUPBY function in PowerBI?

I tried using group by DAX function in PowerBI as Measure, New Column, New Table but I get an error in validating the function, New Table = GROUPBY( 'tab1', 'tab1'[color], "Group By color", …
Dave D.
  • 737
  • 3
  • 10
  • 23
1
2
3
…
99 100