Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have below sample data
id | datetime |
A | 24-01-2022 10:00 |
A | 25-01-2022 15:00 |
Expected output | ||||
Date slicer = 24-01-2022 | id | date | Flag | |
A | 24-01-2022 10:00 | 1 | ||
A | 25-01-2022 15:00 | 0 | ||
Date slicer = 25-01-2022 | id | date | Flag | |
A | 24-01-2022 10:00 | 1 | ||
A | 25-01-2022 15:00 | 0 | ||
Date slicer = 26-01-2022 | id | date | Flag | |
A | 24-01-2022 10:00 | 0 | ||
A | 25-01-2022 15:00 | 1 |
Based on the slicer selection the Flag has to be set accordingly.
Thanks,
Kiran
Solved! Go to Solution.
Hi @Anonymous ,
Output :
Measure:
test =
VAR TEST1 =
FORMAT ( SELECTEDVALUE ( Slicer[Date slicer] ), "yyyy/mm/dd" )
VAR TEST2 =
FORMAT ( MAX ( 'Table'[datetime] ), "YYYY/MM/DD" )
VAR TEST3 =
FORMAT (
"10:00:00",
"HH:MM:SS"
)
VAR TEST4 =
FORMAT ( MAX ( 'Table'[datetime] ), "HH:MM:SS" )
RETURN
IF (
YEAR ( TEST1 ) = 2022
&& MONTH ( TEST1 ) = 1
&& DAY ( TEST1 ) = 24,
IF ( TEST1 >= TEST2, 1, 0 ),
IF (
YEAR ( TEST1 ) = 2022
&& MONTH ( TEST1 ) = 1
&& DAY ( TEST1 ) = 25,
IF ( TEST1 >= TEST2&&TEST3>=TEST4, 1, 0 ),
IF (
YEAR ( TEST1 ) = 2022
&& MONTH ( TEST1 ) = 1
&& DAY ( TEST1 ) = 26,
IF (
MAX ( 'Table'[datetime] )
= CALCULATE ( MAX ( 'Table'[datetime] ), ALL ( 'Table' ) ),
1,
0
)
)
)
)
You could download my pbix file if you need.
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi @Anonymous ,
Output :
Measure:
test =
VAR TEST1 =
FORMAT ( SELECTEDVALUE ( Slicer[Date slicer] ), "yyyy/mm/dd" )
VAR TEST2 =
FORMAT ( MAX ( 'Table'[datetime] ), "YYYY/MM/DD" )
VAR TEST3 =
FORMAT (
"10:00:00",
"HH:MM:SS"
)
VAR TEST4 =
FORMAT ( MAX ( 'Table'[datetime] ), "HH:MM:SS" )
RETURN
IF (
YEAR ( TEST1 ) = 2022
&& MONTH ( TEST1 ) = 1
&& DAY ( TEST1 ) = 24,
IF ( TEST1 >= TEST2, 1, 0 ),
IF (
YEAR ( TEST1 ) = 2022
&& MONTH ( TEST1 ) = 1
&& DAY ( TEST1 ) = 25,
IF ( TEST1 >= TEST2&&TEST3>=TEST4, 1, 0 ),
IF (
YEAR ( TEST1 ) = 2022
&& MONTH ( TEST1 ) = 1
&& DAY ( TEST1 ) = 26,
IF (
MAX ( 'Table'[datetime] )
= CALCULATE ( MAX ( 'Table'[datetime] ), ALL ( 'Table' ) ),
1,
0
)
)
)
)
You could download my pbix file if you need.
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
@Anonymous , Here slicer date has to come from an independent date table
meausre =
var _max = maxx(all('Date') ,[Date])
Var _max2 = maxx(filter(allselected(Table) , Table[Date] <= _max), Table[Date])
return
if(max('table'[Date])=_max2 ,1,0)
@amitchandak Thanks for the reply.
I was not clear enough on the initial question.
id | datetime | |||
A | 24-01-2022 10:00 | |||
A | 25-01-2022 15:00 | |||
B | 24-01-2022 09:00 | |||
B | 25-01-2022 15:00 | |||
Expected output | ||||
Date slicer = 24-01-2022 | id | date | Flag | |
A | 24-01-2022 10:00 | 1 | ||
A | 25-01-2022 15:00 | 0 | ||
B | 24-01-2022 09:00 | 1 | ||
B | 25-01-2022 15:00 | 0 | ||
Date slicer = 25-01-2022 | id | date | Flag | |
A | 24-01-2022 10:00 | 1 | ||
A | 25-01-2022 15:00 | 0 | ||
B | 24-01-2022 09:00 | 1 | ||
B | 25-01-2022 15:00 | 0 | ||
Date slicer = 26-01-2022 | id | date | Flag | |
A | 24-01-2022 10:00 | 0 | ||
A | 25-01-2022 15:00 | 1 | ||
B | 24-01-2022 09:00 | 0 | ||
B | 25-01-2022 15:00 | 1 |
With Slicer 25-01-2022, we need to flag only records prior to 10 AM for that day
With Slicer 26-01-2022, we need to flag latest record prior days any time of the day as shown
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |