Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
How can we be able to get the value of the previous ID dynamically?
I have values that are based on sequence ID or Rank ID. and I want to get the value of the previous ID of the selected ID.
Example (in this example, ID 4 is filtered out, so the expected "previous ID" should be ID 3):
This would be a big help If I can get this.
Thanks!
Solved! Go to Solution.
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.
Hi, @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.
Hi! 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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |