Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to come up with a DAX formula for a calculated column "Last Index" that will return the current index if the NAME has changed from previous value, and hold the last index if the value has not changed. Please see the example data below. I have tried to make an if statement (IF current name <> previous name, index, __________) but I'm getting stuck with the Else. I've also tried LASTNONBLANK but I'm confused by it and getting errors.
Thanks for the help!
Example Data
Index | NAME | Last Index |
1 | A | 1 |
2 | A | 1 |
3 | B | 3 |
4 | B | 3 |
5 | C | 5 |
6 | C | 5 |
7 | C | 5 |
8 | A | 8 |
9 | A | 8 |
10 | C | 10 |
Solved! Go to Solution.
[Last Index] = // calc column
var VeryFirstIndex = MIN( T[Index] )
var CurrIndex = T[Index]
var CurrName = T[Name]
var PrevIndex = CurrIndex - 1
var PrevName =
MAXX(
FILTER(
T,
T[Index] = PrevIndex
),
T[Name]
)
var Result =
switch( true(),
// If we're on the first row,
// return the current index.
CurrIndex = VeryFirstIndex, CurrIndex,
// If the current name is same as
// the prev name, return the value
// of the index for the last different
// name before this name plus 1.
CurrName = PrevName,
MAXX(
FILTER(
T,
T[Name] <> CurrName
&&
T[Index] < CurrIndex
),
T[Index]
) + VeryFirstIndex,
// CurrName <> PrevName
CurrIndex
)
RETURN
Result
Why can't you use a composite model so that you can mix DQ and Import?
Since my source table was changed to Direct Query, I was thinking that it might not refresh correctly if I made the calculated table as Import. I've usually only made models with one or the other, not a composite. I will try a composite model like you suggest. Thank you again for a quick response.
[Last Index] = // calc column
var VeryFirstIndex = MIN( T[Index] )
var CurrIndex = T[Index]
var CurrName = T[Name]
var PrevIndex = CurrIndex - 1
var PrevName =
MAXX(
FILTER(
T,
T[Index] = PrevIndex
),
T[Name]
)
var Result =
switch( true(),
// If we're on the first row,
// return the current index.
CurrIndex = VeryFirstIndex, CurrIndex,
// If the current name is same as
// the prev name, return the value
// of the index for the last different
// name before this name plus 1.
CurrName = PrevName,
MAXX(
FILTER(
T,
T[Name] <> CurrName
&&
T[Index] < CurrIndex
),
T[Index]
) + VeryFirstIndex,
// CurrName <> PrevName
CurrIndex
)
RETURN
Result
This calculated column works great for Import tables, but I have recently switched the data source to be Direct Query and this method does not work with the MAXX function. Is there a method for performing the same calculations with a measure instead of a column, or some other way for Direct Query? I have tried to modify it but I am getting stuck.
This works great, thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |