What - if simulation with non numerical, categorical parameters

[Anonymous]

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 radiobutton slicer do not work.
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

[dedelman_clng · Accepted solution]

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

[dedelman_clng · Accepted solution]

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

[Anonymous]

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 Supplier 

The 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? 

Rohit

[Anonymous · Accepted solution]

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 🙂