Forum Discussion
What - if simulation with non numerical, categorical parameters
Hi Experts,
I have a requirement to use non - numerical categorical parameter for what - if simulation.
Requirement details
Model supply side disruption by letting a few or all of the suppliers to close down business temporarily for the next week and then observe the impact on the projected Inventory for a specific Site and Item/SKU combination (supply chain node). I have the calculated data for Projected Inventory, Confirmed PO Qty for the next 1 week.
Projected Inventory = Total Supply (includes Confirmed PO, On hand Stock, Predicted Inbound Stock) - Total Demand (Forecast and Supplier Commits)
Now I have tried to convert the What - of parameter which creates a CALCULATED Table with Generate Series DAX command into a table of non numeric values i.e. Supplier Codes and then placing this as a radio button filter/slicer in the report. The other MEASURE has the SINGLE value of the supplier that I intend to close for the 1 week period as a part of the simulation.
I have a CALCULATED Column for the adjusted Projected Inventory value with the following DAX code.
Projected Inventory (adj) =
VAR _supplier_shutdown_flg = 'Supplier Shut down flag'[Parameter Value]
VAR _ship_from_site_selected = 'Supplier PO cancellation'[Supplier PO cancellation Value]
VAR _planned_inbound = [Planned Inbound]
VAR _predicted_arrival = [Predicted_Arrivals]
VAR _ship_from_site_curr_row = [Ship from Site (planned inbound/Pred arrivals)]
VAR _projected_inventory_actual = [Projected Inventory]
VAR _result =
IF (
NOT ( ISBLANK ( _supplier_shutdown_flg ) )
&& NOT ( ISBLANK ( _ship_from_site_selected ) ),
_projected_inventory_actual - _planned_inbound - _predicted_arrival,
_projected_inventory_actual
)
RETURN
_result
The logic is
If a specific supplier is selected by the end user, then cancel all the POs that originated from this supplier for the next 1 week and recalculate the projected inventory and highlight the stock outs (-ve Projected Inventory) so that management can plan for an alternative couse of action.
I also explored the possibility of using a custom BUTTON and define a set of actions for the same.
But apparently Power BI do not allow custom action to be defined beyond the usual bookmarks and navigation.
Is there a workaround to build what - if simulation with non numeric parameters?
Thanks,
Rohit
Hi Anonymous -
You should be able to use SELECTEDVALUE inside a measure to determine the selected supplier code. Put that into a variable then use it with FILTER or CALCULATE to make your measures dynamic. Something along the lines of
WhatIfMeasure = VAR __WhatIf = SELECTEDVALUE(Supplier[Supplier Code]) RETURN CALCULATE([put your expression or logic here], FILTER(Supplier, Supplier[Supplier Code] != __WhatIf)If this doesn't help or make sense, please provide a copy of your pbix scrubbed of any sensitive data.
David
Hi dedelman_clng ,
I got this issue resolved now.With multiple selection
when cleared of selections every thing resets
To get the MULTIPLE selection, I used IN and VALUES function
And to ensure that the filters are cleared when nothing is SELECTED I used ISFILTERED check.
This works like a charm now.
Thank you so much David for your directions.Power BI and DAX is fun 🙂
3 Replies
- dedelman_clng
Community Champion
Hi Anonymous -
You should be able to use SELECTEDVALUE inside a measure to determine the selected supplier code. Put that into a variable then use it with FILTER or CALCULATE to make your measures dynamic. Something along the lines of
WhatIfMeasure = VAR __WhatIf = SELECTEDVALUE(Supplier[Supplier Code]) RETURN CALCULATE([put your expression or logic here], FILTER(Supplier, Supplier[Supplier Code] != __WhatIf)If this doesn't help or make sense, please provide a copy of your pbix scrubbed of any sensitive data.
David
Hi dedelman_clng ,
Thank you for your kind response.
I was able to get the SUPPLIER selection to work.
Change in projected inventory when I select 1 SupplierThe change doesn't happen when I select multiple Suppliers because SELECTEDVALUE function can only work with single selection.
The following DAX code was able to get the job done.
Proj Inv (adj with supplier selection) =
VAR _projected_inventory =
CALCULATE ( SUM ( 'Node_Inventory (what if)'[Projected Inventory] ) )
VAR _selected_supplier = 'Selected Vendor'[Selected Vendor Value]
VAR _planned_inbound =
CALCULATE (
SUM ( 'Node_Inventory (what if)'[Planned Inbound] ),
FILTER (
'Node_Inventory (what if)',
'Node_Inventory (what if)'[Ship from Site (planned inbound/Pred arrivals)] = _selected_supplier
)
)
VAR _predicted_inbound =
CALCULATE (
SUM ( 'Node_Inventory (what if)'[Predicted_Arrivals] ),
FILTER (
'Node_Inventory (what if)',
'Node_Inventory (what if)'[Ship from Site (planned inbound/Pred arrivals)] = _selected_supplier
)
)
VAR _result = _projected_inventory - _planned_inbound - _predicted_inbound
RETURN
_result
I have a MEASURE with the What if parameter that captures the use selection in the FILTER.Selected Vendor Value = SELECTEDVALUE('Selected Vendor'[Ship from Site (planned inbound/Pred arrivals)], "")
However, SELECTEDVALUE doesn't allow me to capture multiple values. Is there a way to capture MULTIPLE values? Also, I am not sure how to get MULTIPLE supplier values in the FILTER condition within the CALCULATE statement. Can I use IN statement?
RohitHi dedelman_clng ,
I got this issue resolved now.With multiple selection
when cleared of selections every thing resets
To get the MULTIPLE selection, I used IN and VALUES function
And to ensure that the filters are cleared when nothing is SELECTED I used ISFILTERED check.
This works like a charm now.
Thank you so much David for your directions.Power BI and DAX is fun 🙂