Forum Discussion

mrothschild's avatar
mrothschild
Continued Contributor
3 years ago
Solved

Matrix output from a Measure that uses Virtual Tables

PowerBI file is here: https://drive.google.com/file/d/15UbEraNivloq7pkqOKLUJFhnJLxFwXCi/view?usp=sharing

Excel **output** file is here: https://docs.google.com/spreadsheets/d/15UptSqVU2ky4pDa8Ax7AkGDgfCqfL9bs/edit?usp=sharing&ouid=115111367427670973715&rtpof=true&sd=true

 

The desired output is a Matrix that looks like the Excel spreadsheet. 

 

The Excel file above was copy/pasted data from a Calculated Table in the PBIX file above.  The PBIX table is "zzzCalc Table audit".  In that table, calculations and VARs are based on "hard-coded" input. 

 

The Measure "zzVirtual Table Measure - Static" has the exact same programming as "zzzCalc Table audit", but outputs a Measure instead of a table. 

 

The Measure "zzVirtual Table Measure - Dynamic" is the programming for that I want to include in a Matrix visual.  The difference between ". . .Static" and ". . .Dynamic" is that the Dynamic version is driven by user inputs into the different Slicers on the screen.  

 

Not sure this is possible because of PowerBI's inability to create Calculated Tables from slicer inputs, but the data is all in there, so I'm hoping a work-around exists.

 

Wondering if running the Parameter What-if through a Tile Slicer that is disconnected from everything else - perhaps (throwing spaghetti against a wall) - creating a disconnected slicer to control/filter the Matrix is the workaround, but I'm not familiar how to do it?

 

Thanks!

 

 

 

Need Help
  • mrothschild's avatar
    mrothschild
    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().

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago
    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?

     

    • mrothschild's avatar
      mrothschild
      Continued Contributor
      3 years ago

      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. 

       

       

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        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?

  • mrothschild's avatar
    mrothschild
    Continued Contributor
    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().