Max Calculation

[SkorpionAAM]

New to DAX here. I am trying to calculate the latest record 
i create the new column which  if place_digital_adress is same then Match if not then Not match

_LOOK =

var _val = LOOKUPVALUE('GasStation Survey'[survey_round_id], 'GasStation Survey'[place_digital_adress], 'GasStation Survey'[place_digital_adress],BLANK())

RETURN IF(ISBLANK(_val),"Match","Not Match")

Now i want to Count Latest record because if last year Round 1 is Open and in round 2 is close I want count latest record i tried MAx but not get succes

مفتوح1 = var openx = CALCULATE(COUNT('GasStation Survey'[_LOOK]),FILTER('GasStation Survey','GasStation Survey'[_LOOK] = "Not Match"),FILTER('GasStation Survey','GasStation Survey'[survey_round_id] = "Round 2"),FILTER('GasStation Survey','GasStation Survey'[STATUS_OF_STATION] = "Open")

) return

IF(ISBLANK(openx),0,openx)

if place digital adress have

100-00-00        ROund 1        Match                           22/09/2019       Close

100-00-00        ROund 2        Match                           23/09/2019          open

100-00-01        ROund 2        Not Match                     24/09/2019            Open

100-00-02         ROund 2       Not Match                      24/08/2020            close

100-00-03         ROund 1       Match                              24/09/2019             Close

100-00-03         ROund 2        Match                               25/09/2020           Open

[SkorpionAAM · Accepted solution]

[Greg_Deckler]

@SkorpionAAM - See if Lookup Min/Max meets your needs: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

[amitchandak]

@SkorpionAAM , Try, This measure will give last status based on context

lastnonblankvalue(Table[DAte], max(Table[STATUS_OF_STATION]))

Measusre to count last status as open
countx(filter(summarize(Table,Table[survey_round_id], "_1",lastnonblankvalue(Table[DAte], max(Table[STATUS_OF_STATION]))),[_1]= "open"),[survey_round_id])

[SkorpionAAM]

in this DAX where i can add max Funcation

مفتوح1 = var openx = CALCULATE(COUNT('GasStation Survey'[_LOOK]),FILTER('GasStation Survey','GasStation Survey'[_LOOK] = "Not Match"),FILTER('GasStation Survey','GasStation Survey'[survey_round_id] = "Round 2"),FILTER('GasStation Survey','GasStation Survey'[STATUS_OF_STATION] = "Open")
) return
IF(ISBLANK(openx),0,openx)

[SkorpionAAM]

i have survey ROund 1 and Round two

this is the Place digatl Id where i find the Place Dital Id if we already survey in round 1 then give me Match so we know how many we did new survey in round 2 and how many resurvy..

which is okay

biut know 2nd thing is if round 1 staion is open but now in round 2 station is close now so we need to count as a close because its latest Status of Station

[Greg_Deckler]

@SkorpionAAM - So like this?

Measure 6a = 
    VAR __ID = MAX([ID])
    VAR __Latest = MAX([Date])
    VAR __LatestStatus = IF(MAXX(FILTER(ALL('Table (6)'),[Date]=__Latest && [ID]=__ID),[STATUS_OF_STATION])="Open",1,0)
RETURN
    __LatestStatus

Updated PBIX attached below sig. 

[SkorpionAAM]

no ...

as i said if round 1 Station is close and in round 2 station is open then we count round 2 one

in your Dax its okay 

For Example ID 1 in round 1 station was Open but In round 2 Survey Station is Closed

so Wht im doing is Count how many station is close and open till date now

so in round sttaion is open so now we count Close because in round 2 station is close

in your Dax giving me double count  if chnage Conditon from open to close

[Greg_Deckler · Accepted solution]

@SkorpionAAM So, this? PBIX attached.

Measure 6b = 
    VAR __Table =
    ADDCOLUMNS(
        SUMMARIZE('Table (6)',[ID],"Date",MAX([Date])),
        "Status",MAXX(FILTER('Table (6)',[ID]=EARLIER([ID])&&[Date]=EARLIER([Date])),[STATUS_OF_STATION])
    )
RETURN
    COUNTROWS(FILTER(__Table,[Status]="Open"))

[SkorpionAAM]

let me check thn i back to u

[SkorpionAAM · Accepted solution]

[Greg_Deckler]

@SkorpionAAM - Not work = not helpful. I have mocked up your data and measure in the PBIX attached below sig. What are you looking for in terms of expected output? See Page 6, Measure 6, Table (6)

[SkorpionAAM]

i looking for each ID latets status

[SkorpionAAM]

not working

[Greg_Deckler]

@SkorpionAAM Which one? Both suggestions? What is the expected output from the sample data?

[SkorpionAAM]

i need to count as well with this Method

مفتوح1 = var openx = CALCULATE(COUNT('GasStation Survey'[_LOOK]),FILTER('GasStation Survey','GasStation Survey'[_LOOK] = "Not Match"),FILTER('GasStation Survey','GasStation Survey'[survey_round_id] = "Round 2"),FILTER('GasStation Survey','GasStation Survey'[STATUS_OF_STATION] = "Open")
) return
IF(ISBLANK(openx),0,openx)

[amitchandak]

@SkorpionAAM . Can you share expected output? If the initial one output then share source.