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 ...
user2724541
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?
- 4,358
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, ...
- 39.8k
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")
...
- 6,698
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.
...
- 2,725
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 ...
- 39.8k
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 #.##...
- 757
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 ...
- 215
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 ...
- 5,270
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,...
- 7,849
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 ...
- 186
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:
...
- 113
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 ...
- 1,570
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 ...
- 1,081
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?
- 311
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 ...
- 39.8k
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]),
...
- 93
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] ),
...
- 35.2k
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 ...
- 848
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, ...
- 786
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 ...
- 519
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 ...
- 1,344
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 ...
- 21.1k
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, ...
- 113
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 ...
- 6,698
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:
(...
- 179
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('...
user7577311
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 ...
- 369
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 ...
- 1,425
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 ...
- 4,358
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, ...
- 2,584
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],
...
- 757
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 ...
- 103
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:
...
- 35.2k
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 ...
- 1,091
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([...
- 93
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 ...
- 2,263
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 @...
- 4,358
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,698
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,390
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])))
- 305
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,698
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,698
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, ...
- 283
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 ...
- 1,461
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 ...
- 3,187
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) ...
- 10.7k
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(...
