Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

find first value

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 t...

Tips and Tricks

Anonymous
7 years ago
This has been solved by correcting the data in the database, eradicationg the original problem.

Thank you very much for your assistance!

Icon for Community Champion rank

Community Champion

7 years ago

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

Anonymous
Not applicable
7 years ago
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]
)
)
Anonymous
Not applicable
7 years ago
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
  Microsoft Employee
  7 years ago
  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
    Not applicable
    7 years ago
    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.