I am trying to get a value to repeat across a group in PowerPivot, using DAX.
I've managed to get this kind of windowing to work for summaries in the group, but now I'm trying to get it to work for a text value.
As an example, I have the following table - Orders - in my PowerPivot model:
OrderID ItemName Price IsSelected 1 Printer 100 1 Laptop 200 Y 1 Ink 50 2 Laptop 200 2 Pen 10 Y
I would like to add a calculated column to the model as follows:
OrderID ItemName Price IsSelected Order_SelectedItem 1 Printer 100 Laptop 1 Laptop 200 Y Laptop 1 Ink 50 Laptop 2 Laptop 200 Pen 2 Pen 10 Y Pen
As an aside, the reason I need this is that the users of this model generally group by the OrderID on the resulting pivot, and having a field like this would allow them to see at a glance what the "selected" item for the order was.
I have tried creating a calculated column, SelectedItemName, as a precursor to my windowing function
=IF(Orders[IsSelected]=1, [ItemName], BLANK())
However, the BLANK is interfering with the following, as the VALUES function comes back with too many rows per group:
=CALCULATE(VALUES(Orders[SelectedItemName]); ALLEXCEPT(Orders; Orders[OrderID]))
ALLNOBLANKROW wasn't helpful in fixing this... I've attempted to use SUMMARIZE, various filters, and so on, but I just haven't managed to crack it.
It's frustrating because it seems like something so simple (similar numeric aggregations are quite straightforward), but I haven't found an obvious solution - and I haven't found anyone else with this particular problem.
Many thanks!
You can use something like this in your calculated column
=CALCULATE(
FIRSTNONBLANK(Orders[ItemName],COUNTROWS(Orders))
,FILTER(Orders, [IsSelected] = "Y"
&& Orders[OrderID] = EARLIER(Orders[OrderID]))
)