Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Solved! Go to 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 - See if Lookup Min/Max meets your needs: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
@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])
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)
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
@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.
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
@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"))
let me check thn i back to u
@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)
i looking for each ID latets status
not working
@SkorpionAAM Which one? Both suggestions? What is the expected output from the sample data?
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 . Can you share expected output? If the initial one output then share source.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |