Forum Discussion
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:
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/
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:
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/
