Forum Discussion

Aazam's avatar
Aazam
Icon for Helper I rankHelper I
2 years ago

Get the Max ID (DAX Help Needed)

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

 

 

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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