CUBE like joins from different datasets?

[dancarr22]

Hello,

Is there a way to create CUBE like joins from different datasets?

i.e. We do not have a formal data warehouse/OLAP CUBE.

We do have (example) 2 different data sources:

(1) Positions - This is how many shares we currently own.  i.e.

IBM -- 100 Shares

MSFT -- 200 Shares

These are our current positions which are made up of one or more transactions.

(2) Transactions - These are the transactions which make up the 100 shares of IBM and 200 shares of MSFT from (1).

IBM -- 1/1/15 - BUY 70

IBM -- 1/1/17 - BUY 30

MSFT -- 1/1/15 - BUY 120

MSFT -- 1/1/17 - BUY 80

So, as you can see from the transactions -- if we SUM the transactions by ticker we get 100 for IBM and 200 for MSFT.  This matches what we have in the positions table.  So, we can reconcile that the SUM(Transactions) = Positions.

But, if we just do a LEFT JOIN then it will duplicate the positions for as many transactions as we have.  We want to display the (summarized) positions data -- then click into it and show the transactions -- without duplicating the position data.  Is this possible?

Thanks,

Dan

[clausm73]

I would simply ignore the 'Positions' data source as it contains redundant information. Any 'Position' can be calculated from the 'Transactions' data source with a relevant filter on the share name and/or date.

But you might want to create a "Running Total" measure to show a 'Position' at ANY date in the period. This can be accomplished writing some DAX measure formulas or use buit-in time-intelligence.

Examples:

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

This formula assumes that you have all your transaction dates in a separated but related date table. Such a table can be created by the following formula:

Date = VALUES( 'Transactions'[Date] )

You can find more generalised patterns on this approach here: http://www.daxpatterns.com/usecases/inventory-stock/

[dancarr22]

Thanks for your response. 

The example I provided was a simplification of the actual datasets.  So, the SUM(Transactions) will not usually= positions.  There could be various corporate actions (splits, reinvested dividends, etc) that would cause the #s to not reconcile.

There are also hundreds of other fields in the positions table that we would want to display -- though doing regular JOINs would cause these to be duplicated for the # of transactions.

Also, we may want to join to GDP by country or revenue per country per company (i.e. IBM has 40% of sales in US, 15% in Germany, 20% in China, etc)

Realize I can have different tables and join them in the data relationship view -- but this dups records.

Is there a way in the data relationships to create a virtual OLAP cube -- assuming we cannot -- just curious.  Thought maybe there was some special join type which could accomplish this. 

Thanks again for your response and example,

Dan 

[clausm73 · Accepted solution]

You could create a visual with the relevant 'Positions' - and then use the new "drill through actions" feature to redirect the report user to a detail report with the filtered 'Transactions'.

See a demo on Youtube from Microsoft Data Insights Summit 2017 here:

Opening Keynote Session: James Phillips, Corporate Vice President, Microsoft (MS Data Insights Summi...

Or you can treat the scenario like a "different granularities" problem for the 'Positions' and the 'Transactions' table.

In such a scenario you could adapt a "Budget Pattern" into your model.

A great and generalized pattern for this by @marcorusso and @AlbertoFerrari can be found here:

http://www.daxpatterns.com/budget-patterns/

Another approach where to handle different granularities is by @ImkeF here:

http://www.thebiccountant.com/2017/02/23/blending-data-in-powerbi-like-in-tableau/