Fill in blanks to compare dimension

[Anonymous]

Hi,

I got a table with;

Distance	fkID	Speed
1	ID1	274
2	ID2	273
3	ID1	275
3	ID2	274
4	ID2	274
5	ID1	275
5	ID2	275
6	ID2	276
7	ID1	275
7	ID2	276

When comparing the distance

Distance	ID1	ID2	Delta
1	274		274
2		273	-273
3	275	274	1
4		274	-274
5	275	275	0
6		276	-276
7	275	276	-1

I'm looking for a measure that fill in the blanks based on the previous know record

Distance	ID1	ID2	Delta
1	274		274
2	274	273	1
3	275	274	1
4	275	274	1
5	275	275	0
6	275	276	-1
7	275	276	-1

I tried this measure without success, IF(ISBLANK([Speed]), CALCULATE(LASTNONBLANK(Table1[Speed],0), ALLEXCEPT(Table1, Table1[Distance])), [Speed])

[Anonymous]

Thanks @v-yueyunzh-msft , sorry but in that table there are 1000's different IDs which I could compare not only ID1 or ID2

[v-yueyunzh-msft]

Hi, @Anonymous 

According to your description, you have more than two IDs. And i am not sure how to compare them. But for the show all value in Matrix , you can use this method.

(1)We need to create a dimension table:

Distance = VALUES('Table'[Distance])

(2)Then we create a measure:

Measure = var _SPEED = CALCULATE( SUM('Table'[Speed]) , 'Table'[Distance] = MAX('Distance'[Distance]))

var _t = FILTER('Table' , 'Table'[Distance] <= MAX('Distance'[Distance]))

var _max_dis =MAXX(_t,[Distance])

var _pre_speeed=sumx( FILTER(_t,[Distance]=_max_dis) , [Speed])

RETURN

IF(_SPEED=BLANK(),_pre_speeed,_SPEED)

Then we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft]

Hi , @Anonymous 

According to your description, you want to Fill in blanks to compare dimension.

Here are the steps you can refer to :
(1)Due to the raw data is not the whole data, we need to click "New table" to cretae two dimension tables:

Table 2 = VALUES('Table'[Distance])

Table 3 = ADDCOLUMNS( UNION( VALUES('Table'[fkID]) , {"Delta"}) , "sort" , IF([fkID]="ID1",1,IF([fkID]="ID2",2,3)))

(2)Then we can click "New measure" to create a measure:

Measure2 = var _cur_dis = MAX('Table 2'[Distance])
var _cur_id = MAX('Table 3'[fkID])
var _t = FILTER('Table','Table'[Distance]<=_cur_dis && 'Table'[fkID]= "ID1")
var _max_dis = maxx(_t,[Distance])
var _speed =MAXX( FILTER(_t,[Distance]=_max_dis) , [Speed])
var _t2 = FILTER('Table','Table'[Distance]<=_cur_dis && 'Table'[fkID]= "ID2")
var _max_dis2 = maxx(_t2,[Distance])
var _speed2 =MAXX( FILTER(_t2,[Distance]=_max_dis2) , [Speed])
return
IF(_cur_id="Delta",_speed-_speed2,IF(_cur_id="ID1",_speed,_speed2))

(3)Then we can meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly