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.
Helper IHi! 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 PreviousValue
Next, 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.