Scenario Help

[charles_o]

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- Low Base High. The scenario labels do not exist in my main table and I need to create that link.

Low - Certain

Base - Certain, Expected

High - Certain, Expected, Likely, Hopeful

How can I do this? I tried creating a new table( with Low-Certain; Base- Certain, Expected....) and merging it with my main table but it did not work the way I wanted it to

[v-jiascu-msft]

Hi @charles_o,

Is this your solution?

Scenario =
VAR allLikelyhood =
    CALCULATE (
        CONCATENATEX ( VALUES ( Table1[Likelyhood] ), [Likelyhood], "," ),
        ALLEXCEPT ( Table1, Table1[Project] )
    )
RETURN
    IF (
        allLikelyhood = "Certain",
        "Low",
        IF (
            allLikelyhood = "Certain,Expected",
            "Base",
            IF ( allLikelyhood = "Certain,Expected,Likely,Hopeful", "High", "ERROR" )
        )
    )

Best Regards,

Dale

[charles_o]

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 · Accepted solution]

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

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

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

Best Regards,

Dale

[McCow]

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

[charles_o]

I think we are misunderstanding this a bit.

		Included in
A	CERTAIN	High, Base, Low
B	EXPECTED	High, Base
C	LIKELY	High
D	HOPEFUL	High

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]

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? Do you can specify pls?

[charles_o]

This is the set up for my data. Currently I have graphs, I am using the "slicer" in table3. When I toggle to "Low" on the slicer, it still shows all the data. However, looking at Table3, we can see that "Low" is only related to "Low,Base,High".

 Does not make sense, since I do not see how that should bring up my other scenarios.

Relationship

Table 3Table 1- Main TableTable 2- Middle Table

[v-jiascu-msft]

Hi @charles_o,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[charles_o]

Got it to work! Sorry this took forever!

[McCow]

Oh, @charles_o

I see now how you trouble is.
The all relationships in this case must be activated for "Both" directions not "Single".

Just make changes, as @v-jiascu-msft already described. Look here:

I hope its solved your problem.

Best regs

[McCow]

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

[v-jiascu-msft]

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

Best Regards,

Dale

[McCow]

Hi PBI friends, @charles_o, @v-jiascu-msft

it's was not easy question but i suggest another workaround solution. Possible is can be useful.

Here is the LINK to example code.

And here is example itself:

https://app.powerbi.com/...

If you have a questions ask back.

Best regs