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.
Hi,
Need your help !!
For every user_id there are multiple Progress_id. I am trying to find out the second last progress_id with respect to each user_id. I want to create a column as Result which will have the second last progress_id . Screenshot below.
Solved! Go to Solution.
@shoeb1359 , Try a new measure like
Measure =
VAR __id = MAX ('Table'[user_ID] )
VAR __date = CALCULATE ( MAX('Table'[progress id] ), ALLSELECTED ('Table' ), 'Table'[user_ID] = __id )
CALCULATE ( MAX ('Table'[progress id] ), VALUES ('Table'[user_ID] ),'Table'[user_ID] = __id,'Table'[progress id] < __date )
new column =
var _max = maxx(filter(Table, [user_ID] =earlier([user_ID]) ),[progress id])
return
maxx(filter(Table, [user_ID] =earlier([user_ID]) && [progress id] <_max ),[progress id])
@shoeb1359 , Try a new measure like
Measure =
VAR __id = MAX ('Table'[user_ID] )
VAR __date = CALCULATE ( MAX('Table'[progress id] ), ALLSELECTED ('Table' ), 'Table'[user_ID] = __id )
CALCULATE ( MAX ('Table'[progress id] ), VALUES ('Table'[user_ID] ),'Table'[user_ID] = __id,'Table'[progress id] < __date )
new column =
var _max = maxx(filter(Table, [user_ID] =earlier([user_ID]) ),[progress id])
return
maxx(filter(Table, [user_ID] =earlier([user_ID]) && [progress id] <_max ),[progress id])
Thanks, Amit for the Solution. Surely I will Implement this.
Although one solution I figured out earlier, please suggest this approach feasible in terms of performance.
Rank =
RANKX (
FILTER (
table,
table[User_ID] = EARLIER ( table[User_ID] )
),
table[Progress ID],
,
ASC
)
Another column :
Progress id_2=
CALCULATE (
MAXX(
table,
CALCULATE(
SUM(table[Progress ID]),
FILTER(
table,
table[User_ID] = EARLIER (table[User_ID] )
&& table[Rank]
=EARLIER ( table[Rank] )-1
),
ALLEXCEPT(table,table[User_ID])
)
),
ALLEXCEPT(table,table[User_ID])
)
Third column:
Previous profile = IF(table[Progress id_2]=BLANK(),table[Teacher_ID],table[Progress id_2])
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |