Forum Discussion
Writing an IF statement referencing a column, not a measure
I have a table(Part Number Master) with a column titled "BOM". The results in that Column is either null or B.
I currently have the following measure locted in the Part Number Master table:
Order = (((sum('Part Number Master'[TOTAL DAYS LEAD TIME])/7)+2.8)*'Part Number Master'[Avg Weekly Consumption])-sum('52 Week Consumption'[On Hand])-sum('52 Week Consumption'[On Order])
I want to make a change to the measure were if the part number has a "B" in the BOM column, change the formula to "+3.8" instead of the current "+2.8". Something like this:
Order = if('Part Number Master'[BOM]="B", (((sum('Part Number Master'[TOTAL DAYS LEAD TIME])/7)+3.8)*'Part Number Master'[Avg Weekly Consumption])-sum('52 Week Consumption'[On Hand])-sum('52 Week Consumption'[On Order]),(((sum('Part Number Master'[TOTAL DAYS LEAD TIME])/7)+2.8)*'Part Number Master'[Avg Weekly Consumption])-sum('52 Week Consumption'[On Hand])-sum('52 Week Consumption'[On Order]))
The problem is when I type =if(.........it will not allow for a column entry, it only allows me to select other "Measures".
Basically, if the part number BOM column has a B, I want to order an extra week's worth of inventory.
Hi Anonymous,
What data source are you using in this case? Based on my research, the error is returned by the MS SSAS server.
Reference:
https://archive.sap.com/discussions/thread/3840305
Regards
4 Replies
- Greg_Deckler
Community Champion
You need to use an aggregation function, in this case you should be able to use VALUES function.
v-ljerr-msftMicrosoft Employee
Hi Anonymous,
In addition, using FIRSTNONBLANK Function (DAX) should also work in this scenario. The formula below is for your reference.
Order = IF ( FIRSTNONBLANK ( 'Part Number Master'[BOM], 1 ) = "B", ( ( ( SUM ( 'Part Number Master'[TOTAL DAYS LEAD TIME] ) / 7 ) + 3.8 ) * 'Part Number Master'[Avg Weekly Consumption] ) - SUM ( '52 Week Consumption'[On Hand] ) - SUM ( '52 Week Consumption'[On Order] ), ( ( ( SUM ( 'Part Number Master'[TOTAL DAYS LEAD TIME] ) / 7 ) + 2.8 ) * 'Part Number Master'[Avg Weekly Consumption] ) - SUM ( '52 Week Consumption'[On Hand] ) - SUM ( '52 Week Consumption'[On Order] ) )Regards
- v-ljerr-msft
Hi Anonymous,
What data source are you using in this case? Based on my research, the error is returned by the MS SSAS server.
Reference:
https://archive.sap.com/discussions/thread/3840305
Regards
