Forum Discussion

dancarr22's avatar
dancarr22
Helper V
9 years ago
Solved

CUBE like joins from different datasets?

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

Need Help
Tutorial Requests

3 Replies

  • clausm73's avatar
    clausm73
    Advocate II
    9 years ago

    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's avatar
      dancarr22
      Helper V
      9 years ago

      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