PowerPivot DAX Text Windowing Function to repeat a value across a group

2
votes

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!

1
excel-2013powerpivotdaxpowerbi

1 Answers

2
votes

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]))
 )