Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want to get Max Change_id and in my Total Resigned Member Dax it max change id should be 1 and the other should be 0, Screenshot and DAX is attached below
Total Resigned Members =
//var code = SELECTEDVALUE('MM Members Type and Status History-Flag'[change_code]) = 7
var Maxdate = SELECTCOLUMNS('Calendar',"month",max('Calendar'[Date]))
var Maxdate2 = max('Calendar'[Date])
var Mindate2 = min('Calendar'[Date])
var mindate = SELECTCOLUMNS('Calendar',"year",min('Calendar'[Date]))
var maxID =
CALCULATE
(
MAXX('MM Members Type and Status History-Flag','MM Members Type and Status History-Flag'[change_id]),
FILTER(all('MM Members Type and Status History-Flag'), 'MM Members Type and Status History-Flag'[startDate] >= mindate2
&& 'MM Members Type and Status History-Flag'[startDate] <= Maxdate2
))
VAR StatusID = SELECTCOLUMNS('MM Member Status',"ids",'MM Member Status'[status_id])
VAR TypeID = SELECTCOLUMNS('MM Member Type',"ids",'MM Member Type'[member_type_id])
var result =
CALCULATE(
COUNT('MM Members Type and Status History-Flag'[member_id] ),
FILTER (
'MM Members Type and Status History-Flag',
//('MM Members Type and Status History-Flag'[change_id] = maxID) &&
'MM Members Type and Status History-Flag'[startDate] >= mindate2
&& 'MM Members Type and Status History-Flag'[startDate] <= Maxdate2
&& 'MM Members Type and Status History-Flag'[member_status_id] in StatusID
&& 'MM Members Type and Status History-Flag'[changeType] = "Status Change"
&& 'MM Members Type and Status History-Flag'[member_type_id] in TypeID
)
// ,USERELATIONSHIP('MM Members'[member_id],'MM Members Type and Status History-Flag'[member_id])
)
RETURN result
Hi @Aazam,
Here are measure formulas that can be used to get the last change_id and show the flag based on check if current startdate equal to last change_id date. You can try to use these formulas if them helps:
last ChangeID=
VAR currDate =
MAX ( 'MM Members Type and Status History-Flag'[startDate] )
VAR _lastDate =
CALCULATE (
MAX ( 'MM Members Type and Status History-Flag'[startDate] ),
ALLSELECTED ( 'MM Members Type and Status History-Flag' ),
VALUES ( 'MM Members Type and Status History-Flag'[member_id] )
)
VAR _lastChangeID =
CALCULATE (
MAX ( 'MM Members Type and Status History-Flag'[change_id] ),
FILTER (
ALLSELECTED ( 'MM Members Type and Status History-Flag' ),
[startDate] = _lastDate
),
VALUES ( 'MM Members Type and Status History-Flag'[member_id] )
)
RETURN
_lastChangeID
last changed flag =
VAR currDate =
MAX ( 'MM Members Type and Status History-Flag'[startDate] )
VAR _lastDate =
CALCULATE (
MAX ( 'MM Members Type and Status History-Flag'[startDate] ),
ALLSELECTED ( 'MM Members Type and Status History-Flag' ),
VALUES ( 'MM Members Type and Status History-Flag'[member_id] )
)
RETURN
IF ( currDate = _lastDate, 1, 0 )
Regards,
Xiaoxin Sheng
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |