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().
The desired output is a Matrix that looks like the Excel spreadsheet.
UX design red flag right there.
Instead of all the What-If parameters you could use the filter pane.
What is the business insight you are trying to support?
The users want to be able to input the single numbers (as in PBIX) and get the output (as in PBIX) but want to see the periodic (in this case) monthly figures. This is a leasing template to evaluate investment returns and users insist on seeing the underlying numbers that produce the investment returns.
Beyond that, I suspect some graphs of periodic cash flows and sensitivity/compare-contrast analysis will be requested, but until I generate the first part, the subsequent stuff is moot.
- lbendlin3 years ago
Super User
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?
- mrothschild3 years ago
Continued Contributor
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.