Forum Discussion

charles_o's avatar
charles_o
Helper I
8 years ago
Solved

Scenario Help

Hi- I have data that is organized by project in rows with a column of expectations of likelyhood.   Certain Expected Likely Hopeful   I need to then be able to sort this data into 3 scenarios-...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi charles_o,

     

    You can try the approach of McCow. Another method.

    1. Create a middle table for all the scenario.

    Scenario

    Low
    Base
    High
    Low,Base
    Low,High
    Base,High
    Low,Base,High

    2. Create a table for slicer.

    SlicerScenario

    LowLow
    BaseBase
    HighHigh
    LowLow,Base
    BaseLow,Base
    LowLow,High
    HighLow,High
    BaseBase,High
    HighBase,High
    LowLow,Base,High
    BaseLow,Base,High
    High

    Low,Base,High

    3. Create relationships and change the filter direction to Both.

    Scenario_Help2

     

    Best Regards,

    Dale

charles_o's avatar
charles_o
Helper I
8 years ago

 

 

EDIT: Got to what I wanted with this- One last ask below!

 

 

Scenario =

IF (
Table1[Likelyhood] = "Certain",
"Low,Base,High",
IF (
Table1[Likelyhood] = "Expected",
"Base,High",
IF ( Table1[Likelyhood] = "Likely", "High",IF (Table1[Likelyhood] = "Hopeful", "High")
)
)
)

Looks like this:

 

 

 

I want to use a slicer on my dashboard to display the data by "Low",  "Base", or "High". Currently my options are "Low,base,High", "Base,High", "high".

 

Any Suggestions?

 

 

 

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi charles_o,

 

You can try the approach of McCow. Another method.

1. Create a middle table for all the scenario.

Scenario

Low
Base
High
Low,Base
Low,High
Base,High
Low,Base,High

2. Create a table for slicer.

SlicerScenario

LowLow
BaseBase
HighHigh
LowLow,Base
BaseLow,Base
LowLow,High
HighLow,High
BaseBase,High
HighBase,High
LowLow,Base,High
BaseLow,Base,High
High

Low,Base,High

3. Create relationships and change the filter direction to Both.

Scenario_Help2

 

Best Regards,

Dale

  • McCow's avatar
    McCow
    Resolver III
    8 years ago

    Fantatic!

    Thanks v-jiascu-msft

    I change a little "Scenario" column:

    Scenario = 
IF(SEARCH("likely";[Likelyhood];1;0)>0 || SEARCH("hopeful";[Likelyhood];1;0)>0 ;"High";
    IF(SEARCH("expected";[Likelyhood];1;0)>0;"Base";
        IF(SEARCH("certain";[Likelyhood];1;0)>0;"Low";
            "Error"
        )//high
     )//base
  )//low

    And THIS is a cAsE independent solution.

     

    Best regs

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Thank you for sharing, McCow. It's wonderful.

       

      Best Regards,

      Dale

    • charles_o's avatar
      charles_o
      Helper I
      8 years ago

      I think we are misunderstanding this a bit.

       

        Included in
      ACERTAINHigh, Base, Low
      BEXPECTEDHigh, Base
      CLIKELY High
      DHOPEFULHigh

       

      Think about it like this

       

      Low = A

      Base = A + B

      High = A + B+ C + D

       

      So when I select "High", the data for Low and Base must also be included.

      • McCow's avatar
        McCow
        Resolver III
        8 years ago

        Yes, charles_o, right!

         

        charles_o wrote:

         

        ......

         

        So when I select "High", the data for Low and Base must also be included.

         

        And I don't see the collision. Our last solution is completely adjustable. All possible combinations must be in the table "Solution" described. I personally include this solution in my personally DAX-gems (thank v-jiascu-msft). Or somewhere exist some trouble? Specify pls