Decision table (without nested IFs or Power Query)

%3CLINGO-SUB%20id%3D%22lingo-sub-2878257%22%20slang%3D%22en-US%22%3EDecision%20table%20(without%20nested%20IFs%20or%20Power%20Query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2878257%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20decision%20table%20(%3CA%20href%3D%22https%3A%2F%2Fen.wikipedia.org%2Fwiki%2FDecision_table%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Egeneral%20info%20here%3C%2FA%3E)%20that%20I%20would%20like%20to%20use%20in%20Excel.%3C%2FP%3E%3CP%3EI%20know%20how%20to%20implement%20it%20using%26nbsp%3B%20nested%20IFs%20or%20Power%20Query%2C%20but%20is%20there%20a%20simple%20and%20efficient%20way%20%3CU%3Ewithout%3C%2FU%3E%20these%3F%20By%20using%20some%20other%20smart%20setup%20or%20strategy%3F%3C%2FP%3E%3CP%3ESimplified%20example%20attached.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bartvana_0-1635062478847.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F319500i48A27CD9EE310C5D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bartvana_0-1635062478847.png%22%20alt%3D%22bartvana_0-1635062478847.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2878257%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2878290%22%20slang%3D%22en-US%22%3ERe%3A%20Decision%20table%20(without%20nested%20IFs%20or%20Power%20Query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2878290%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863782%22%20target%3D%22_blank%22%3E%40bartvana%3C%2FA%3E%26nbsp%3BWould%20you%20mind%20explaining%20the%20logic%20behind%20the%20result%20table%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2878433%22%20slang%3D%22en-US%22%3ERe%3A%20Decision%20table%20(without%20nested%20IFs%20or%20Power%20Query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2878433%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20of%20course.%3C%2FP%3E%3CP%3EThe%20decision%20table%20reads%20like%20this%3A%3C%2FP%3E%3CUL%3E%3CLI%3Eif%20MulOfVol%20%3D%201%2C%20then%20ID%201%20(the%20other%20parameters%20are%20irrelevant%20in%20this%20row%2C%20that's%20why%20they%20are%20empty)%3C%2FLI%3E%3CLI%3Eif%20MulOfVol%20%3D%200%20and%20Custom%20%3D%201%2C%20then%20ID%202%3C%2FLI%3E%3CLI%3E...%3C%2FLI%3E%3CLI%3Eif%20SKUCrit01Fail%20%3D%201%20and%20SKUCrit02Fail%20%3D%201%20and%20SKUCrit03Fail%20%3D%201%2C%20then%20ID%205%3C%2FLI%3E%3C%2FUL%3E%3CP%3EBased%20on%20the%20fact%20table%2C%20I%20need%20to%20fill%20in%20the%20corresponding%20decision%20ID%20for%20each%20SKU.%3C%2FP%3E%3CP%3E(I%20just%20noticed%20an%20error%20in%20my%20decision%20table%2C%20corrected%20file%20attached%2C%20I'll%20also%20correct%20my%20initial%20post).%3C%2FP%3E%3CP%3ESo%20SKU1%20gets%20decision%20ID%201%20because%20MulOfVol%20%3D%201.%3C%2FP%3E%3CP%3ESKU03%26nbsp%3B%20gets%20decision%20ID%207%20because%20its%20parameters%20correspond%20with%20this%20row%20(where%20empty%20fields%20are%20irrelevant).%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

I have a decision table (general info here) that I would like to use in Excel.

I know how to implement it using  nested IFs or Power Query, but is there a simple and efficient way without these? By using some other smart setup or strategy?

Simplified example attached.

bartvana_0-1635072738067.png

 

 

4 Replies

@bartvana Would you mind explaining the logic behind the result table?

Yes, of course.

The decision table reads like this:

  • if MulOfVol = 1, then ID 1 (the other parameters are irrelevant in this row, that's why they are empty)
  • if MulOfVol = 0 and Custom = 1, then ID 2
  • ...
  • if SKUCrit01Fail = 1 and SKUCrit02Fail = 1 and SKUCrit03Fail = 1, then ID 5

Based on the fact table, I need to fill in the corresponding decision ID for each SKU.

(I just noticed an error in my decision table, corrected file attached, I'll also correct my initial post).

So SKU1 gets decision ID 1 because MulOfVol = 1.

SKU03  gets decision ID 7 because its parameters correspond with this row (where empty fields are irrelevant).

@bartvana I gladly leave it to someone else.

@bartvana 

 

Nothing is simple in excel, however you can setup a data model and use measures either in power pivot or Power-Bi to generate your dashboard or report to get the desired results.