Forum Discussion

SkorpionAAM's avatar
SkorpionAAM
Helper V
5 years ago
Solved

Max Calculation

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('GasSt...
Need Help
Show and Tell
Tips and Tricks
  • SkorpionAAM's avatar
    SkorpionAAM
    5 years ago

    Capture.PNG

    Capture.PNG

  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    @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"))
amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
    SkorpionAAM
    Helper V
    5 years ago

    not working

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

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

      • SkorpionAAM's avatar
        SkorpionAAM
        Helper V
        5 years ago

        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)

  • SkorpionAAM's avatar
    SkorpionAAM
    Helper V
    5 years ago

    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)

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      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's avatar
        SkorpionAAM
        Helper V
        5 years ago

        i looking for each ID latets status

    • SkorpionAAM's avatar
      SkorpionAAM
      Helper V
      5 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        5 years ago

        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.