Only fill 1st row with value

[Anonymous]

Hi, 

I have a table where the end user wants to download the data, but make some manipulations to two columns after downloading ('factor' and 'factor Inc'), which would impact the final formula of 'Sku Increase'

I put a string of the formula they want to use in the 'Sku Increase' column, figuring they could enable the function once they download the data. 

Is there a way to only have this string show on the first row? 

• Right now it refers to row 4 for every single row. 
• It should only show for the first row no matter how they sort the table . 

Extra information 

When downloaded, the table looks like this in Excel. 

• You can see the formula in column AV all refer to row 4

Currently, the user has to click on AV4 and hit enter to make the formula work

Then if they drag that cell down it will work - But I'd prefer not to have possible user error having all these other rows populated with row 4's reference in case of user error. 

The formula right now in Power BI just creates the text string of the formula. 

Thank you

[bcdobbs · Accepted solution]

Really interesting approach.

Don't think you can limit it to only top row without some sort of row ID to filter the dax on.

Instead could you use excel table references? Eg [@[ColumnName]] which would reduce possible error?

https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-23...

[bcdobbs · Accepted solution]

Just another thought, could they do the manipulation live in the service with what if parameters:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

[bcdobbs · Accepted solution]

Really interesting approach.

Don't think you can limit it to only top row without some sort of row ID to filter the dax on.

Instead could you use excel table references? Eg [@[ColumnName]] which would reduce possible error?

https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-23...

[Anonymous]

You are fantastic, thank you @bcdobbs !

[bcdobbs · Accepted solution]

Just another thought, could they do the manipulation live in the service with what if parameters:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

[Anonymous]

I love this idea, and would have gone with this one as it's quite elegant.  However, the end users are downloading a decent amount  distinct skus and stores, and each store or product can have different variables to adjust - so they're thinking it'd be easier to do that part in Excel.  I'm going to go with your other suggestion on this one, but will keep this in mind for the future, thanks!

[AlexisOlson · Accepted solution]

Both of these suggestions are good. Table references should be row-independent but a what-if parameter along with a measure does seem like a cleaner solution.

There's a bit of a wrinkle in that you can't use a dynamic parameter to modify a calculated column but I'm confident you could create an equivalently functioning measure unless the columns include recursive logic that cannot be refactored.

@Anonymous In this simplest case where those columns are just constants, you don't need to export anything to tinker with those factors. Set up the two what-if parameters and use them in a measure like

Sku Increase = [Factor value] * [Factor Inc value] * [Adj Qty measure]

[Anonymous]

@AlexisOlson  This great - I really appreciate both of your ideas!