find first value

[Anonymous]

I have the following two tables:

Order and OrdLn

I would like to find OrdLn[Price] where OrdLn[OrdNo] = Order[IDBakssystem] and OrdLn[ProdNo] = "TS-L" and store it in an calculated column TS-L in the Order table.

Unfortunately there are sometimes several instansces where this is true, and this results in an error.

I have looked at similar problems on the forum, but none that matches this or help me solving it.

I have tried with LOOKUP and CALCULATE and FILTERS using FIRSTNONBLANKS and EARLIER without luck.

OrdLn[OrdNo] is also a number and Order[IDBakssystem] is a text, so this needs to be converted with VALUE

[v-danhe-msft]

Hi @Anonymous ,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Regards,

Daniel He

[Anonymous · Accepted solution]

This has been solved by correcting the data in the database, eradicationg the original problem.

Thank you very much for your assistance!

[Anonymous]

Hi!

Thank you for the follow up.

No it is not solved yet as I have been given other task temporary at work, but I'll let you know as soon as I have tested it.

[PattemManohar]

@Anonymous  Please post the sample test data (that can be copied easily) and expected output to suggest an accurate solution.

[Anonymous]

OrdLn

OrdNo	ProdNo	Price
73013	TS-L	8000
73013	TS-L	0
73013	10300000	390,456
73013	T4	0
73013	T4	0
73013	T4	0
73013	TS-L	0
73013		0

However with the formula I thought worked, it still returns 0 not 8000.

[v-danhe-msft]

Hi @Anonymous ,

I am afraid you could not achieve this feature if you have no index for your data column, the FIRSTNONBLANK will just return the minum value for your data:

https://www.sqlbi.com/blog/marco/2015/02/24/use-firstnonblank-and-lastnonblank-instead-of-min-and-max-dax/

Regards,

Daniel He

[Anonymous]

Thank you for the reply!

I have an Unique column "LnNo", and I suppose I could use the lowes denominator there as it would be the highest probability to be correct, however not guaranteed.

[v-danhe-msft]

Hi @Anonymous ,

Ok, if your problem could be solved, don't forget to close this topic.

Regards,

Daniel He

[Anonymous]

I'll try it out in the upcomming days, and post the result.

Thank you for your assistance!

[Anonymous]

OK! 🙂

I think I solved it by creating two Columns:

Value_IDBaksystem = IFERROR(VALUE( Ordre[IdBaksystem] );0)

And then

TS-L =

CALCULATE (

FIRSTNONBLANK( OrdLn[Price] ; 1 );

FILTER (

OrdLn;

OrdLn[ProdNo] = "TS-L" && OrdLn[OrdNo] = Ordre[Value_IDBaksystem]

)

)