Forum Discussion
Only fill 1st row with value
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
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?
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
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]
6 Replies
- bcdobbs
Community Champion
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?
- bcdobbs
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
- AlexisOlson
Super User
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]
You are fantastic, thank you bcdobbs !