Forum Discussion
Matrix output from a Measure that uses Virtual Tables
- 3 years ago
Here is the solution:
Measure = VAR _StrtDate = EOMONTH(2023,12,1) + 1 VAR _LeaseTermTotal = // 60 _LeaseTermFirst + _LeaseTermSecond + _LeaseTermTransition VAR _EndDate = EOMONTH(_StrtDate,_LeaseTermTotal+1) VAR _CalMth = GENERATESERIES (0, DATEDIFF (_StrtDate, _EndDate, MONTH ), 1 ) VAR Virtual_Table_1.0 = ADDCOLUMNS( _CalMth, "Date", IF([Value]=0,EOMONTH(_DeploymentDate,[Value]),EOMONTH(_StrtDate,[Value]-1)+1) ) VAR Virtual_Table_1.1 = ADDCOLUMNS( Virtual_Table_1.0, "@AcqCost_1", _AcquisitionPrice_First, "@GrossCF", SWITCH(TRUE() , [Value] = 0, -1 * _AcquisitionPrice_First , [Value] = _LeaseTermFirst + 1 && _LeaseTermTotal = _LeaseTermFirst, _ExitPrice_Scenario_First , [Value] = _LeaseTermFirst + 1 && _LeaseTermTotal <> _LeaseTermFirst, _CashFlow_Gross_Second , [Value] = _LeaseTermTotal + 1, _ExitPrice_Scenario_Second , [Value] < _LeaseTermFirst + 1, _CashFlow_Gross_First , [Value] < _LeaseTermTotal + 1, _CashFlow_Gross_Second , 1234 ) ) RETURN [@GrossCF]This provides the ability to generate a Matrix of Measures out of a virtual table driven by Parameter Slicer SELECTEDVALUES().
I don't see any formulas in the Excel sheet? What's the logic for the table?
Also, what's the role of the R visual?
The Excel is a copy/paste from the calculated table that is in PBIX referenced above with "hard-coded" inputs. Not sure which R visual you're referring to, but probably wouldn't be able to answer anyway. R is installed for other PBIX files, and maybe this one is using "Advanced Cards" somewhere.
If I can figure out how to limit/filter/slice the "Value" on the "Duplicate of Summary Model" tab to the user input to "Lease Term (months)" slicer, I think I can brute force the Matrix with a bunch of if/thens.