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,
I am trying to create a measure which will show the delta time between two dates in a column.
If the rows have same ID and the LEVEL is blank, it should show the time between the current row and the previous row which meet the criteria.
Any sugestions ?
I succeded an now my Datediff give me what I want.
Earlier = IF(ISBLANK('Table'[LEVEL]); DATEDIFF(CALCULATE(MAX('Table'[CREATED]); FILTER(ALL('Table'); 'Table'[ID] = EARLIER('Table'[ID]) && ISBLANK('Table'[LEVEL]) && 'Table'[CREATED] < EARLIER('Table'[CREATED]) ) );'Table'[CREATED];MINUTE); BLANK() )
This is obvious just a test table, in my real table the "Table[ID]" is related to an GroupID in another table (one to many) where the column "Field" with the same "GroupID" is equal to "assignee"
Now I would like to add a criteria saying: Only do DATEDIFF for rows where "Table[LEVEL] is blank AND related "Field" column contain the value "assignee"
var currentid = Table[ID] var currentdate = Table[Created] return IF (currentid = 1, BLANK(), currentdate- CALCULATE (MAX (Table[Created]), FILTER(Table, Table[ID] = currentid && Table[ID] < currentdate) ) )
I love this question - calculated columns get tricky when we're doing a calculation in one row and we have to access the contents of other rows. The pattern here is to use variables (var ... return) and FILTER
Hi austinsense,
This looks very interesting I have newer tried to work with variable in DAX.
But the result is not quiet what I expected. Let me try to refraise.
I would like to have a column containing the amount of time from the date in the current CREATED column until the date in the CREATED column in where [ID]=[ID] && ISBLANK(Table[LEVEL]).
I have sort of been able to do that with calculated columns by using EARLIER, there I could get the time between each row but not jump the rows which were not blank.
An I would like to use measure instead.
The new measure should show 24 hours (or any other unit) in row 3 considering that the CREATED date in row 2 and 3 is blank and they belong to the same ID
Again it should show 72 hours in row 9 because that is the time between CREATED date in row 6 until row 9 which is the next blank LEVEL with ID 2.
Row 10 should show 24 hours.
I appriciate any feedback, thanks
It was early in the morning and I didn't write this correctly the first time
//For the calculated column var currentid = Table[ID] var currentlevel= Table[Level] return IF (currentlevel= 1, BLANK(), currentdate- CALCULATE (MIN(Table[Created]), FILTER(Table, currentlevel<>1, Table[ID] = currentid) ) )
I can help you write this as a measure but I'm tied up for a while - maybe someone else could jump in and help out?
Hi,
I got a little closer, but not yet there.
Right now I am using MIN('Table'[CREATED]) as my first date in DATEDIFF but it supposed to be "PREVIOUS" date if the previous line meet my criterium.
I expect I have to use EARLIER somehow? or do anyone have any suggestions how to get a result where line number three will become1440 minutes (like line number two in this example) and not the difference from the first line that meet my criterium.
If anybody can do this in a measure it would be great.
Between = var currentid = 'Table'[ID] var currentlevel= 'Table'[LEVEL] var currentdate= 'Table'[CREATED] return IF(ISBLANK(currentlevel); DATEDIFF(CALCULATE (MIN('Table'[CREATED]); FILTER('Table'; ISBLANK(currentlevel) && 'Table'[ID] = currentid)); currentdate; MINUTE); BLANK() )
Appreciate any suggestions
forgot to upload a picture...
Hi @eacy & @ImkeF & @austinsense ,
I have similar problem, could someone please help me out with DAX for below as I am stuck in middle. Below is my sample data
Resource Name DIS_scheduled Scan Time
TEST_CONCURRENT_SCANS_ORA_SM_8 SM_PROFILING_PRD 12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_7 SM_PROFILING_PRD 12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_6 SM_PROFILING_PRD 12/26/2023 14:30
TEST_CONCURRENT_SCANS_ORA_SM_5 SM_PROFILING_PRD 12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_4 SM_PROFILING_PRD 12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_3 SM_PROFILING_PRD 12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_2 SM_PROFILING_PRD 12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_1 SM_PROFILING_PRD 12/26/2023 13:00
TEST_CONCURRENT_SCANS_ORA_MD_5 MD_PROFILING_PRD 12/27/2023 12:00
TEST_CONCURRENT_SCANS_ORA_MD_4 MD_PROFILING_PRD 12/27/2023 12:00
TEST_CONCURRENT_SCANS_ORA_MD_3 MD_PROFILING_PRD 12/27/2023 13:30
TEST_CONCURRENT_SCANS_ORA_MD_2 MD_PROFILING_PRD 12/27/2023 15:00
TEST_CONCURRENT_SCANS_ORA_MD_1 MD_PROFILING_PRD 12/27/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_3 LG_PROFILING_PRD 12/28/2023 13:00
TEST_CONCURRENT_SCANS_ORA_LG_2 LG_PROFILING_PRD 12/28/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_1 LG_PROFILING_PRD 12/28/2023 9:00
TEST_CONCURRENT_SCANS_ORA_XL_2 XL_PROFILING_PRD 12/29/2023 10:00
TEST_CONCURRENT_SCANS_ORA_XL_1 XL_PROFILING_PRD 12/30/2023 7:00
TEST_CONCURRENT_SCANS_ORA_SM_8 SM_PROFILING_PRD 12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_7 SM_PROFILING_PRD 12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_6 SM_PROFILING_PRD 12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_5 SM_PROFILING_PRD 12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_4 SM_PROFILING_PRD 12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_3 SM_PROFILING_PRD 12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_2 SM_PROFILING_PRD 12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_SM_1 SM_PROFILING_PRD 12/31/2023 13:00
TEST_CONCURRENT_SCANS_ORA_MD_5 MD_PROFILING_PRD 12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_4 MD_PROFILING_PRD 12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_3 MD_PROFILING_PRD 12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_2 MD_PROFILING_PRD 12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_MD_1 MD_PROFILING_PRD 12/31/2023 11:00
TEST_CONCURRENT_SCANS_ORA_LG_3 LG_PROFILING_PRD 12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_LG_2 LG_PROFILING_PRD 12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_LG_1 LG_PROFILING_PRD 12/31/2023 9:00
TEST_CONCURRENT_SCANS_ORA_XL_2 XL_PROFILING_PRD 12/31/2023 7:00
TEST_CONCURRENT_SCANS_ORA_XL_1 XL_PROFILING_PRD 12/31/2023 7:00
so here
>> any resource should not be scheduled(i.e. Scan_time column above) if there are resources already scheduled upto 2 hrs before for DIS_scheduled = "SM_PROFILING_PRD"
>> any resource should not be scheduled if there are resources already scheduled upto 5 hrs before for DIS_scheduled = "MD_PROFILING_PRD"
>> any resource should not be scheduled if there are resources already scheduled upto 10 hrs before for DIS_scheduled = "LG_PROFILING_PRD"
>> any resource should not be scheduled if there are resources already scheduled upto 24 hrs before for DIS_scheduled = "XL_PROFILING_PRD"
So for Example, For DIS_scheduled = "SM_PROFILING_PRD", if 2 Resources have Scan_time on 12/31/2023 14:30 but there are already resources having Scan_time at 12/31/2023 13:00 which is an issue as no resource should be scheduled 2hrs before
I have to use Scan_Time column to check above conditions and create new column with output as "Issue" or "No issue"
Please let me know if more data is required.
Thanks
This is the M-code that would do the trick in the query editor:
let Source = YourImportStep, ChgType = Table.TransformColumnTypes(Source,{{"ID", type text}, {"LEVEL", type text}, {"CREATED", type datetime}}), AddIndex = Table.AddIndexColumn(ChgType, "Index", 1, 1), FilterBlankRows = Table.SelectRows(AddIndex, each ([LEVEL] = "")), AddIndexFilter = Table.AddIndexColumn(FilterBlankRows, "IndexFilter", 0, 1), Durations = Table.AddColumn(AddIndexFilter, "Duration", each try if [ID]=AddIndexFilter[ID]{[IndexFilter]-1} then ([CREATED]-AddIndexFilter[CREATED]{[IndexFilter]-1})*24*60 else null otherwise null), DurationsMin = Table.TransformColumnTypes(Durations,{{"Duration", type number}}), MergeBoth = Table.NestedJoin(AddIndex,{"Index"},DurationsMin,{"Index"},"NewColumn",JoinKind.LeftOuter), Expand = Table.ExpandTableColumn(MergeBoth, "NewColumn", {"Duration"}, {"Duration"}), Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}) in Sort
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |