Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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:
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/
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
62 | |
18 | |
16 | |
13 |