# Questions tagged [dax]

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

9,476
questions

57
votes

5
answers

64k
views

### What's the difference between DAX and Power Query (or M)?

I have been working on Power BI for a while now and I often get confused when I browse through help topics of it. They often refer to the functions and formulas being used as DAX functions or Power ...

21
votes

5
answers

194k
views

### How to get month name from month number in Power BI?

I have Year number and Month Number in my data.
How using DAX can I get the month name out of month number?
In SSRS its very easy. But how to achieve that using DAX?

18
votes

3
answers

6k
views

### Recursion in DAX

I don't know if this is even possible, but I'd like to be able to create a calculated column where each row is dependent on the rows above it.
A classic example of this is the Fibonacci sequence, ...

17
votes

4
answers

32k
views

### DAX Calculate function with and without FILTER

What is the difference in results of CALCULATE function if we use it with and without FILTER function. Suppose we have those two measures:
Measure1 = CALCULATE([X], 'FactTable'[Color]="Red")
...

16
votes

6
answers

199k
views

### Converting an Integer to a Text Value in Power BI

I’m creating a calculated column in a Power BI report. The calculated column concatenates integer and text columns. I tried using the below query to accomplish this, but it resulted in a syntax error.
...

16
votes

1
answer

2k
views

### Force DAX SWITCH function to use strict (lazy) short-circuit evaluation

Set up:
Similar to this question on a MSDN forum, I have a measure that switches between various other measures (some of them much more complex than others). The measure looks like this (my actual ...

15
votes

2
answers

106k
views

### How to format new measure (not column) in power BI as percentage?

When I create a new measure with formula, i.e. sum(col1)/max(col2), it automatically converts the result into #.#% format. Strangely, it doesn't happen all the time; sometimes it just gives result #.##...

14
votes

3
answers

61k
views

### DAX formula to concatenate three columns

I am mew to DAX. How can I concatenate three different columns say First_Name, Middle_Name and Last_Name to a single column with a space in between using CONCATENATE function in DAX.
At present I ...

14
votes

1
answer

84k
views

### Power BI (DAX): Distinct Count Filtered by Condition

I have a data set with publishers with revenue by month. Publishers are considered to be "active" month if their revenue is equal or greater than 1000 for a given month.
Now, I want to count the ...

14
votes

3
answers

3k
views

### Dynamic DAX Number Format

I want to dynamically change the number format of a DAX measure, based on a dimension value (or indeed, based on the order of magnitude of the measure value).
I understand I can use SWITCH and FORMAT,...

11
votes

1
answer

90k
views

### Power BI, DAX--How do I count rows in one table based on values in another table?

I have two tables, lets call them Table1 and Table2. Table1 has a column of unique values, Table2 has a column with the same values but repeated.
What I am trying to accomplish is to calculate the ...

11
votes

2
answers

67k
views

### Combine PowerBI DAX Filter and SELECTCOLUMN

I want to create a new table based on this one:
that filters for Warehouse=2 and "drops" the columns "Price" and "Cost" like this:
I have managed to apply the filter in the first step using:
...

10
votes

7
answers

20k
views

### No QUARTER() in DAX? Really?

While building a Calendar table with PowerQuery for a PowerPivot model in Excel 2013 I use its Date.QuarterOfYear function to get the number of the quarter.
Building the same thing in SSAS Tabular ...

10
votes

7
answers

55k
views

### 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 ...

10
votes

3
answers

17k
views

### DAX - Last Value

I have this table
I would like to create measurement get the last traded value for each day. E.g.
How the DAX query should look like?

10
votes

3
answers

15k
views

### Multiple Linear Regression in Power BI

Suppose I have a set of returns and I want to compute its beta values versus different market indices. Let's use the following set of data in a table named Returns for the sake of having a concrete ...

9
votes

6
answers

4k
views

### 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]),
...

9
votes

1
answer

50k
views

### 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] ),
...

9
votes

2
answers

1k
views

### 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 ...

8
votes

1
answer

116k
views

### Dax expression equivalent to ISNOTBLANK()

I'm trying to create a PBI column in Dax than evaluates if a DateTime column is not blank. There currently is not an ISNOTBLANK() clause in Power BI so I can't outright reference that.
However, ...

8
votes

1
answer

17k
views

### 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 ...

8
votes

2
answers

46k
views

### 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 ...

8
votes

1
answer

15k
views

### 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 ...

8
votes

1
answer

13k
views

### 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, ...

8
votes

3
answers

21k
views

### 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 ...

7
votes

4
answers

74k
views

### Using Multiple filters in DAX

I'm new to DAX.
I'm currently using Power BI and trying to create a total sum of sales that use a few different SKUs (or IDs)
I can use this to filter down to 1 sku ("A1"):
Measure = CALCULATE([...

7
votes

5
answers

53k
views

### Remove total value for one column in PowerBI

I have a table visualisation in PowerBI that sums the top 10 products sold by sales quantity. I have a calculated column which shows the rate of sale, using other fields from the data source:
(...

7
votes

1
answer

61k
views

### DAX FILTER function with multiple criteria

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

7
votes

1
answer

34k
views

### 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 ...

7
votes

2
answers

16k
views

### Calculating Percent of Total in Power Pivot Model

I have created a power pivot table as shown in the picture. I want to calculate quarter over quarter sales change. For which I have to divide for example corporate family "Acer" 's sales in 2012Q4 by ...

7
votes

1
answer

4k
views

### 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 ...

7
votes

1
answer

42k
views

### 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, ...

7
votes

1
answer

20k
views

### 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],
...

7
votes

1
answer

8k
views

### Power BI: TopN and All Other

I have a data set that resembles the following:
Year Location Type Amount
2015 West Apple 12
2015 West Pear 14
2015 East Apple 55
2015 South ...

7
votes

2
answers

32k
views

### Using DATEADD and NOW() together

If I try adding the following Measure into my DimDate table:
Past6Months =
IF(
FIRSTDATE( 'Dates'[FullDate] ) >= DATEADD( NOW(), -7, MONTH ),
TRUE(),
FALSE()
)
I get this error:
...

7
votes

1
answer

299
views

### 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 ...

6
votes

4
answers

123k
views

### Multiple IF Statements in DAX

I currently have Column Data formulated below in Power BI which I need for it to display in one column but replacing the "1" with a Text value being:
Orginal column formula:
Age (18-27) = IF(AND([...

6
votes

8
answers

33k
views

### How to convert seconds into hh:mm format in Power Bi?

In Power Bi, I have a table that contains Name and TimeSpent by user in seconds.
I want to convert total seconds spent by all users into duration format (hh:mm)
When I am getting seconds in hh:mm ...

6
votes

3
answers

26k
views

### How to JOIN summarized data from two queries into new table in DAX Power BI

I have 2 queries:
Premium:
and Losses:
How can I simply summarize data from Premium query and LEFT JOIN it to summarized data in Losses query using DAX?
In SQL it would be like that:
declare @...

6
votes

3
answers

21k
views

### DAX number format with a plus or minus sign

I want to format DAX measure so that value 0.105 is displayed as +10.5%, and value -0.105 is displayed as -10.5%. Important for me is the plus sign. I failed using FORMAT(measure, "+0.0%").
I cannot ...

6
votes

1
answer

47k
views

### Power BI DAX : Get sum of a column based on another

Let say I have the folowwing table :
Client Sales
A 1000
A 100
A 10
B 1000
B 10
C 1
I would like to add another column Names TotalClient at the end ...

6
votes

1
answer

52k
views

### How to calculate cumulative Total and % in DAX?

This might be very simple...
I have the below summary table in Power BI and need to build a Pareto Chart, what I'm looking for is a way to create columns "D" and "E"... Thanks in advance!
The Count ...

6
votes

2
answers

33k
views

### Dax How to get distinct values from a column

This is the query I'm trying.
EVALUATE
SELECTCOLUMNS('MyTable',"col1",DISTINCT(VALUES('MyTable'[Email])))

6
votes

1
answer

9k
views

### Filter SUMMARIZECOLUMNS

How to construct filter tables for SUMMARIZECOLUMNS function?
The SUMMARIZECOLUMNS has the following pattern:
SUMMARIZECOLUMNS(
ColumnName1, ...
ColumnNameN,
FilterTable1, -- my ...

6
votes

3
answers

3k
views

### DAX Difference between naked SUM and sum wrapped in CALCULATE

What is the difference between naked SUM and sum wrapped in CALCULATE?
Measure1 = SUM( tab[col1]) )
Measure2 = CALCULATE ( SUM( tab[col1]) ) )
Update.
I was given an answer to other SO question, ...

6
votes

2
answers

19k
views

### How to force powerpivot relationship to be one-to-one?

Actually, I would be satisfied just getting to pick the direction of the one-to-many. I have one table, the result of a SQL query that I want to use as a lookup table. It is unique in its key column, ...

6
votes

1
answer

32k
views

### Using RELATED function in DAX with USERELATIONSHIP

I am trying to pull in a field from another table in my BISM model using the "RELATED" function. Since there are many tables that have relationship to the People table, only ONE relationship is active ...

6
votes

2
answers

10k
views

### DAX expression for ROW_NUMBER() PARTITION BY ORDER BY equivalent

I have a SQL statement like this:
(ROW_NUMBER() OVER (PARTITION BY a.[market], [MEASURE_TYPE]
ORDER BY AM, REP, ORDER_KEY)) AS ORDER_KEY
I want to write a DAX to implement the ...

6
votes

2
answers

226
views

### DAX Service recall rate measure

I'm struggling to write/calculate this measure in DAX. The definition of recall rate is count of repeat service bookings (count of distinct booking number, should be distinct anyway but just in case) ...

6
votes

1
answer

4k
views

### Running totals with if and max value DAX

I want to create a table about leave and need a cummulative total.
The Annual Leave Column2 DAX is
Annual Leave Column2 =
CALCULATE (
SUM (Sheet1[Debit/Credit]),
ALL ( Sheet1 ),
FILTER(...