Forum Discussion
Select Start Price from Earliest Date
Try this Calculated Column using DAX
Start Price = CALCULATE ( FIRSTNONBLANK ( TableName[Price], 1 ), FILTER ( ALLEXCEPT ( TableName, TableName[Fund Name] ), TableName[Index] = 1 ) )
Try this Calculated Column using DAX
Start Price =
CALCULATE (
FIRSTNONBLANK ( TableName[Price], 1 ),
FILTER ( ALLEXCEPT ( TableName, TableName[Fund Name] ), TableName[Index] = 1 )
)Thank you so much Zubair_Muhammad
May I ask you to explain the formula, new to Power BI so learning the ropes
Thanks again!
HI KM007
Here goes my effort to explain
I am not very good at it :smileytongue:1) Since this is calculated column.... calculation is done on a ROW by ROW basis.
2) The second argument of Calculate i.e.FILTER ( ALLEXCEPT ( TableName, TableName[Fund Name] ), TableName[Index] = 1 )
is used to narrow the scope of computation. This returns a Table with a single row i.e. the Table row with the same Fund Name and where Index =1
3) Finallly the first argument of Calculate returns that PRICE VALUE for that single row
FIRSTNONBLANK ( TableName[Price], 1 )
You can alternatively use
VALUES( TableName[Price])
as well
FirstNonBlank helps pick a single value in case Filter function returns more than one ROW
Brilliantly explained - thanks!
