Forum Discussion
Post PatronGet selected value from slicer to filter caltulate table
Hi Community!
I have 2 fact tables Actual and Scenario ( weekly snapshot data for last 4 weeks) and dimention table which filter Scenario table, need to find ID from Actual table which are not present in selected scenario data and calculate amount for these records. I know that correct way to do this is via virtual table but have troubles how to get filtered Scenario table dynamically. Data model you can see below:
First I've tried to create virtual table with except but is not dynamical
__NewTest =
var _vAct = SELECTCOLUMNS( Data, "ID", Data[ID])
var _vSc = SELECTCOLUMNS( ScenarioData, "ID", ScenarioData[ID])
var _vNew = EXCEPT( _vAct, _vSc)
return
ADDCOLUMNS(
_vNew, "Amount", CALCULATE( SUM( Data[Amount]))
)
Than I went with calculatetable but selectedvalue or switch is not getting neded ID
except =
var _vSelecedScenario = SELECTEDVALUE(Scenario[ScenarioID])
return
CALCULATETABLE(
SELECTCOLUMNS(ScenarioData, "ID", ScenarioData[ID]),
ScenarioData[ScenarioID] = _vSelecedScenario
)
--Not working
CALCULATETABLE(
SELECTCOLUMNS(ScenarioData, "ID", ScenarioData[ID]),
ScenarioData[ScenarioID] = 2
)
--working
What did I miss and how I can get these lists based on selected ScenarioID value?
You can try creating a measure like
__NewTest = VAR _vAct = VALUES ( Data[ID] ) VAR _vSc = CALCULATETABLE ( VALUES ( ScenarioData[ID] ), REMOVEFILTERS ( 'Data' ) ) VAR _vNew = EXCEPT ( _vAct, _vSc ) RETURN CALCULATE ( SUM ( Data[Amount] ), _vNew )and use that in a visual with columns from the Data table.
2 Replies
johnt75Super User
You can try creating a measure like
__NewTest = VAR _vAct = VALUES ( Data[ID] ) VAR _vSc = CALCULATETABLE ( VALUES ( ScenarioData[ID] ), REMOVEFILTERS ( 'Data' ) ) VAR _vNew = EXCEPT ( _vAct, _vSc ) RETURN CALCULATE ( SUM ( Data[Amount] ), _vNew )and use that in a visual with columns from the Data table.
