Forum Discussion
Helper IGet the value of the previous ID dynamically based on sequence ID/ Rank ID
Hi, EvanGetsItDone
If you want to filter dynamically with slicers, you'll need to create a metric instead of the original ID. Start by creating two calculated columns to calculate the data in the previous row.
Previous ID = VAR CurrentID = 'Table'[ID] VAR PreviousID = CALCULATE(MAX('Table'[ID]), FILTER('Table', 'Table'[ID] < CurrentID)) RETURN PreviousID Previous Value = VAR CurrentID = 'Table'[ID] VAR PreviousID = CALCULATE(MAX('Table'[ID]), FILTER('Table', 'Table'[ID] < CurrentID)) VAR PreviousValue = LOOKUPVALUE('Table'[Value], 'Table'[ID], PreviousID) RETURN PreviousValueNext, create a new table.
Then create a measure that when the ID is selected, the row data disappears
SelectID = VAR _slicer = MAX('Table 2'[ID]) RETURN IF(MAX('Table'[ID])<>_slicer,MAX('Table'[ID]),BLANK())How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Memorable MemberHi, EvanGetsItDone
I used the data provided in your picture:
I used the following DAX expression to create a metric:
Previous ID =
VAR _table1 =
CALCULATETABLE ( ALL ( 'Table4' ), 'Table4'[ID] <> 4 )
VAR _table =
SUMMARIZE (
_table1,
Table4[ID],
'Table4'[value],
"prevous ID",
CALCULATE (
MAX ( 'Table4'[ID] ),
FILTER ( ALL ( 'Table4' ), 'Table4'[ID] < SELECTEDVALUE ( 'Table4'[ID] ) ),
REMOVEFILTERS ( 'Table4'[ID] )
)
)
VAR _seletedID =
SELECTEDVALUE ( 'Table4'[ID] )
VAR _ID =
MAXX ( FILTER ( _table, 'Table4'[ID] = _seletedID ), [prevous ID] )
RETURN
IF ( _ID = 0, " ", IF ( _ID = 4, _ID - 1, _ID ) )The result in table is as follows:
With this previous metric in place, I created a second metric using the following DAX expression:
Previous value =
VAR _curID = [Previous ID]
RETURN
CALCULATE (
MAX ( 'Table4'[value] ),
FILTER ( ALL ( Table4 ), 'Table4'[ID] = _curID )
)The full results are shown below:
Best Regards,
hackcrr
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
EvanGetsItDoneHi! Thanks for spending time helping me figuring this out. however I don't specifically need the ID 4 to be filtered out since it would be based of a slicer that the user will choose, however in my post ID 4 is just an example.
I need it to be dynamic, depending on what the user selects on the slicer on which one to filter out or not.
Thanks tho!Hi, EvanGetsItDone
If you want to filter dynamically with slicers, you'll need to create a metric instead of the original ID. Start by creating two calculated columns to calculate the data in the previous row.
Previous ID = VAR CurrentID = 'Table'[ID] VAR PreviousID = CALCULATE(MAX('Table'[ID]), FILTER('Table', 'Table'[ID] < CurrentID)) RETURN PreviousID Previous Value = VAR CurrentID = 'Table'[ID] VAR PreviousID = CALCULATE(MAX('Table'[ID]), FILTER('Table', 'Table'[ID] < CurrentID)) VAR PreviousValue = LOOKUPVALUE('Table'[Value], 'Table'[ID], PreviousID) RETURN PreviousValueNext, create a new table.
Then create a measure that when the ID is selected, the row data disappears
SelectID = VAR _slicer = MAX('Table 2'[ID]) RETURN IF(MAX('Table'[ID])<>_slicer,MAX('Table'[ID]),BLANK())How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.