cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Usates Member
Member

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

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Writing an IF statement referencing a column, not a measure

Hi @Usates,

 

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-op...

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

 

Regards

4 REPLIES 4
Super User
Super User

Re: Writing an IF statement referencing a column, not a measure

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


v-ljerr-msft Super Contributor
Super Contributor

Re: Writing an IF statement referencing a column, not a measure

Hi @Usates,

 

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

Usates Member
Member

Re: Writing an IF statement referencing a column, not a measure

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

v-ljerr-msft Super Contributor
Super Contributor

Re: Writing an IF statement referencing a column, not a measure

Hi @Usates,

 

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-op...

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

 

Regards