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('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

Need Help
Show and Tell
Tips and Tricks
  • 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"))

15 Replies

  • 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

      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)