cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper II
Helper II

Re: CUBE like joins from different datasets?

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/

View solution in original post

3 REPLIES 3
Highlighted
Helper II
Helper II

Re: CUBE like joins from different datasets?

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/

Highlighted
Helper III
Helper III

Re: CUBE like joins from different datasets?

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 

 

Highlighted
Helper II
Helper II

Re: CUBE like joins from different datasets?

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/

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications