In DAX create a new column from a 1st one filterd by a 3rd one depending on intervals

[Anglonso02]

Dear All

I am struggling to create a new column that copies the values from a first one but only the ones form an interval of a third

In the exampel below ( form excel) i ´d need the to get the column "Time beteween 1 and 2" by coping the column "Time" but only the rows between ( or equal ) to 1 and 2.  Leaving blank otherwise

Your help will be much appreciated

Thanks

Anglo.

[kpost · Accepted solution]

1)Use "Fill down" on your [class] column in power query so your second column becomes something like this:

class
1
1
1
2
2
2
2
2
1
1
2
2
2
etc.

Then, in Power Query, follow these instructions to add a row index WITHIN each group of values in your [class] column.
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

so it looks like this:

class, row_index_in_group
1 , 1
1 , 2
1 , 3
2 , 1
2 , 2 
2 , 3
2 , 4
2 , 5
1 , 1
1 , 2
2 , 1
2 , 2
2 , 3

Now the logic should be readily apparent!

Time_between_1_and_2 =

SWITCH(

    TRUE(),

    [class] = 1, [time],

    [class] = 2 && [row_index_in_group] = 1, [time],

    BLANK()

)

In plain english, after you "fill down" and add the row index within each grouping of 1 and 2, it logically follows from your example that ANYTHING where the class is 1 needs to be included.  but when the class is 2, ONLY when it's the first instance of a 2 (the row_index_in_group is a 1).

//Mediocre Power BI Advice, but it's free!//

[Anglonso02 · Accepted solution]

Worked! Thanks for your helped. Appreciated

[Anglonso02]

Ok, Thanks a lot for your prompt answers. i was about to reply right now but I will work on this new aproach you give it to me. I ´ll let you know my progress. Rgds !!

[kpost · Accepted solution]

1)Use "Fill down" on your [class] column in power query so your second column becomes something like this:

class
1
1
1
2
2
2
2
2
1
1
2
2
2
etc.

Then, in Power Query, follow these instructions to add a row index WITHIN each group of values in your [class] column.
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

so it looks like this:

class, row_index_in_group
1 , 1
1 , 2
1 , 3
2 , 1
2 , 2 
2 , 3
2 , 4
2 , 5
1 , 1
1 , 2
2 , 1
2 , 2
2 , 3

Now the logic should be readily apparent!

Time_between_1_and_2 =

SWITCH(

    TRUE(),

    [class] = 1, [time],

    [class] = 2 && [row_index_in_group] = 1, [time],

    BLANK()

)

In plain english, after you "fill down" and add the row index within each grouping of 1 and 2, it logically follows from your example that ANYTHING where the class is 1 needs to be included.  but when the class is 2, ONLY when it's the first instance of a 2 (the row_index_in_group is a 1).

//Mediocre Power BI Advice, but it's free!//