Writing an IF statement referencing a column, not a measure

[Anonymous]

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.

[v-ljerr-msft]

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

[Anonymous]

Here is the error I am receiving when using your suggestion.  Is this an issue with my database or just with the formula?

[v-ljerr-msft · Accepted solution]

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://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec842da4-2cfa-4f2b-95e3-8627ba18f622/the-operation-was-cancelled-because-of-locking-conflicts?forum=sqlanalysisservices

https://archive.sap.com/discussions/thread/3840305

Regards

[Greg_Deckler]

You need to use an aggregation function, in this case you should be able to use VALUES function.