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.
Hi,
This is my first post, below is my data and this is what I want to achieve:
for each ID->find the latest status-> corresponding date field
Name | ID | date | status |
setting 1 | 29 | 3/9/2021 1.42 PM | 0 |
setting 1 | 29 | 3/9/2021 1.43 PM | 0 |
setting 1 | 29 | 3/9/2021 1.44 PM | 0 |
setting 1 | 29 | 3/9/2021 1.45 PM | 1 |
setting 1 | 29 | 3/9/2021 1.46 PM | 1 |
setting 1 | 29 | 3/9/2021 1.47 PM | 1 |
setting 1 | 29 | 3/9/2021 1.48 PM | 1 |
setting 1 | 29 | 3/9/2021 1.49 PM | 1 |
setting 2 | 30 | 3/9/2021 1.42 PM | 0 |
setting 2 | 30 | 3/9/2021 1.43 PM | 0 |
setting 2 | 30 | 3/9/2021 1.44 PM | 0 |
setting 2 | 30 | 3/9/2021 1.45 PM | 1 |
setting 2 | 30 | 3/9/2021 1.46 PM | 1 |
setting 2 | 30 | 3/9/2021 1.47 PM | 0 |
setting 2 | 30 | 3/9/2021 1.48 PM | 0 |
setting 2 | 30 | 3/9/2021 1.49 PM | 0 |
setting 2 | 30 | 3/9/2021 1.50 PM | 1 |
setting 2 | 30 | 3/9/2021 1.51 PM | 0 |
Based on the above data for setting 1-> the latest state change is from 0 to 1-> date field to return is 3/9/2021 1.45 PM
similarly for setting 2 -> the latest state change is from 1 to 0 -> date field to return is 3/9/2021 1.50 PM
data to extract
setting 1->3/9/2021 1.45PM
setting 2 ->3/9/2021 1.50PM
I am able to get the state to go from either 0 to 1 or 1 to 0, any help would be appreciated.
Thanks
RS
Solved! Go to Solution.
@rsreepathi
Please try the below code:
LatestDate =
VAR _Table =
ADDCOLUMNS (
SUMMARIZE ( 'Data', Data[Name], Data[date], Data[status] ),
"@PreviousStatus",
VAR _PreviousDate =
CALCULATE (
MAX ( 'Data'[date] ),
ALL ( 'Data' ),
'Data'[date] < EARLIER ( Data[date] )
)
VAR _PreviousStatus =
CALCULATE (
MAX ( 'Data'[status] ),
ALLEXCEPT ( Data, 'Data'[Name], Data[date] ),
'Data'[date] = _PreviousDate
)
RETURN
_PreviousStatus
)
VAR _FilteredTable =
FILTER ( _Table, 'Data'[status] <> [@PreviousStatus] && 'Data'[status] = 1 )
RETURN
IF( HASONEVALUE( Data[Name] ) , CALCULATE ( MAX ( 'Data'[date] ), _FilteredTable ) )
@rsreepathi
Please try the below code:
LatestDate =
VAR _Table =
ADDCOLUMNS (
SUMMARIZE ( 'Data', Data[Name], Data[date], Data[status] ),
"@PreviousStatus",
VAR _PreviousDate =
CALCULATE (
MAX ( 'Data'[date] ),
ALL ( 'Data' ),
'Data'[date] < EARLIER ( Data[date] )
)
VAR _PreviousStatus =
CALCULATE (
MAX ( 'Data'[status] ),
ALLEXCEPT ( Data, 'Data'[Name], Data[date] ),
'Data'[date] = _PreviousDate
)
RETURN
_PreviousStatus
)
VAR _FilteredTable =
FILTER ( _Table, 'Data'[status] <> [@PreviousStatus] && 'Data'[status] = 1 )
RETURN
IF( HASONEVALUE( Data[Name] ) , CALCULATE ( MAX ( 'Data'[date] ), _FilteredTable ) )
@rsreepathi , with name this measure should work
calculate(max(table[date]), filter(Table, Table[status]=1))
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 |
---|---|
52 | |
27 | |
24 | |
13 | |
9 |
User | Count |
---|---|
74 | |
58 | |
47 | |
16 | |
12 |