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/
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/