Scenarios on FactTable

[Manuel123]

Dear Helpers,

I hope you can help me. I have a Fact Table like the example below:

Date	Scenario	Fact
31.12.2021	1	1.000
31.12.2021	2	1.000
31.01.2022	1	1.000
31.01.2022	2	2.000

In the real table there are way more scenarios, Facts and information. I would like to provide my colleagues the opportunity to select two Cases and Compare them with each other. I tried to help me with one slicer on the Scenario Field, and one Slicer that is a distinct copy of the Scenarios and unlinked to the Fact Table. I thought it would be possible to get Scenario 1 with sum(Fact) and Scenario 2 with calculate(sum(Fact),all(Facttable),Filter(Facttable, Scenario = selectedvalue(DistinctScenarioTable))). However, this did not work. Maybe I overthink this? I guess there should be two measures (Scenario 1 Value & Scenario 2 Value). How would be the best way to achieve this?

Best regards

Manuel

[v-jayw-msft · Accepted solution]

Hi @Manuel123 ,

So you want two measures based on the selected scenrio in slicer?

measure1 is Scenario1 with sum(Fact).

 -- always return sum(Fact) of Scenario1?

measure2 is Scenario2 with calculate(sum(Fact),all(Facttable),Filter(Facttable, Scenario = selectedvalue(DistinctScenarioTable))).

--return sum(Fact) of selected scenario in slicer?

If I understand you correctly, you could refer below formulas.

measure 1 = calculate(sum('table'[fact]),filter(allselected('table'),'table'[scenario] = "scenario1"))

measure 2 = calculate(sum('table'[fact]),filter(allselected('table'),'table'[scenario] = selectedvalue('slcier'[scenario])))
Please correct me if I misunderstood your meaning.

Best Regards,

Jay