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
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.
Solved! Go to 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://archive.sap.com/discussions/thread/3840305
Regards
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
Here is the error I am receiving when using your suggestion. Is this an issue with my database or just with the formula?
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
You need to use an aggregation function, in this case you should be able to use VALUES function.
User | Count |
---|---|
158 | |
109 | |
96 | |
84 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |