Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Good day, Community.
I am working on a project where I need to calculate the difference in [Cycles] for like [Registration] values for each previous instance of an event in a table, where the order of events is based on the [PRIMARYKEY] of the table. The first instance of each can be left blank.
I have worked most of this out in a calculated column, but keep running into the issue of using like registration values only. It seemed simple to me at first, but I am missing something. Any help is greatly appreicated. Below is a link to some sample data. I have included a screenshot as well.
Solved! Go to Solution.
Disregard... Just got it to work. Thank you for looking, though! Leaving this hear in case anyone else could use.
Cycles Since Previous =
VAR CurrentReg = 'Table'[Registration]
VAR CurrentKEY = 'Table'[PRIMARYKEY]
VAR PrevKEY = CALCULATE(
MAX('Table'[PRIMARYKEY]),
FILTER(
'Table',
'Table'[PRIMARYKEY] < CurrentKEY &&
'Table'[Registration] = CurrentReg
)
)
RETURN
IF(
ISBLANK(PrevKEY),
BLANK(),
'Table'[ Cycles ] -
CALCULATE(
MAX('Table'[ Cycles ]),
FILTER(
'Table',
'Table'[PRIMARYKEY] = PrevKEY
)
))
Disregard... Just got it to work. Thank you for looking, though! Leaving this hear in case anyone else could use.
Cycles Since Previous =
VAR CurrentReg = 'Table'[Registration]
VAR CurrentKEY = 'Table'[PRIMARYKEY]
VAR PrevKEY = CALCULATE(
MAX('Table'[PRIMARYKEY]),
FILTER(
'Table',
'Table'[PRIMARYKEY] < CurrentKEY &&
'Table'[Registration] = CurrentReg
)
)
RETURN
IF(
ISBLANK(PrevKEY),
BLANK(),
'Table'[ Cycles ] -
CALCULATE(
MAX('Table'[ Cycles ]),
FILTER(
'Table',
'Table'[PRIMARYKEY] = PrevKEY
)
))
User | Count |
---|---|
81 | |
75 | |
70 | |
42 | |
36 |
User | Count |
---|---|
114 | |
56 | |
52 | |
43 | |
42 |