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.
Dear DAX Experts ,
I have a doubt , i dont know if this is possible or not with DAX
I have a table
Primary Dateand time | Secondary Date and time |
8/2/17 2:30 AM | 8/2/17 4:30 AM |
8/2/17 4:35 AM | 8/4/17 5:30 AM |
8/4/17 5:25 AM | 8/4/17 6:30 AM |
This table is recorded one by one i mean secondary date and time in row one must be logically less than primary date and time from second row ,and similarly ahead .
But some time while recording data , i have found some overlapping ,
So how can i flag such events
My desired output will look something like this
Primary Dateand time | Secondary Date and time | Overlap Flag |
8/2/17 2:30 AM | 8/2/17 4:30 AM | 0 |
8/2/17 4:35 AM | 8/4/17 5:30 AM | 0 |
8/4/17 5:25 AM | 8/4/17 6:30 AM | 1 |
Please help
Regards
Sujit Thakur
@Sujit_Thakur - Shouldn't both the last two entries be flagged for overlap?
Dear @Greg_Deckler ,
Only one must be flagged on from which overlapping occured .
@Sujit_Thakur - So that is based on Index then? So the rule is as long as a row does not overlap any previous rows (based on index) then it should not be flagged, correct?
Dear @Greg_Deckler
Yes , actually the example which i gave above also contains a index column for every model .
e.g
Pvin primary date Secondary date
1
1
1
1
1
2
2
2
So here the catch is those objects with same Pvin must be checked for no overlapping , one more point i would like to add that there is no index other than pvin
Hi @Sujit_Thakur ,
USe DateDIFF function.
DATEDIFF ( <Date1>, <Date2>, <Interval> )
The result is positive if Date2 is larger than Date1.
Incase you get a negative value, flag it as 1 using IF statement.
Regards,
Harsh Nathani
Create a Column
Previous SecondaryDate =
CALCULATE(MAX('Table'[Secondary Date and time]), FILTER('Table','Table'[Secondary Date and time] < EARLIER('Table'[Secondary Date and time])))
Column = IF( DATEDIFF('Table'[Primary Dateand time ],'Table'[Previous SecondaryDate],SECOND) < 0 , 0,1)
Regards,
Harsh Nathani
@Sujit_Thakur , I solved the overlapping problem in this file. Refer if this can help
https://www.dropbox.com/s/1mlq21o1xjuw8il/overlappingdates.pbix?dl=0
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |