Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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.

 

1 ACCEPTED SOLUTION

4 REPLIES 4
v-ljerr-msft
Employee
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

Anonymous
Not applicable

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

 

Capture.PNG

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.