Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
dancarr22
Helper V
Helper V

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

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
clausm73
Helper III
Helper III

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/

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 

 

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/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors