Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I am rather new to power BI and I do not know if this is possible but I would like to solve the following problem. I have two tables.
Table 1, transactional data (example data, one entry per purchase):
Table 2, partially aggregated data (example data, e.g. distance per month, aggregated per vehicle and month):
Expected result: Table 3 (I know that there are 2 owners of vehicle ID002 but let's assume that owner and vehicle are unique in month), the problem ist of course to add the distance to the owner / vehicle and if applicable aggragate it for month/year etc.:
Is there a way how to achieve it? Do I need to add a measure to table 1? Thank you!
Solved! Go to Solution.
hi to achieve this i loaded in your data and also create bridge tables like so
Calendar table can be create with dax using Modeling > New Table
Calendar = ADDCOLUMNS ( CALENDAR (MIN('Table 1'[Date]), MAX('Table 1'[Date])), "Week Number", FORMAT ( [Date], "ww" )) ***you can add other colums to fit needs**
Vehicle can also be made with follow code, you can reference which ever table has more IDs or if you have a list of known IDs you can load it in:
VEH = DISTINCT('Table 1'[Veh])
I then use matrix visual with the following fields
ROWS:
'Table1'[Owner]
'VEH'[VEH]
COL:
'Calendar'[Date]
VAL:
'Table1'[Price]
'Table2'[Distance]
Out come:
EDIT:
You can turn of Row Subtotals to remove the totals
hi to achieve this i loaded in your data and also create bridge tables like so
Calendar table can be create with dax using Modeling > New Table
Calendar = ADDCOLUMNS ( CALENDAR (MIN('Table 1'[Date]), MAX('Table 1'[Date])), "Week Number", FORMAT ( [Date], "ww" )) ***you can add other colums to fit needs**
Vehicle can also be made with follow code, you can reference which ever table has more IDs or if you have a list of known IDs you can load it in:
VEH = DISTINCT('Table 1'[Veh])
I then use matrix visual with the following fields
ROWS:
'Table1'[Owner]
'VEH'[VEH]
COL:
'Calendar'[Date]
VAL:
'Table1'[Price]
'Table2'[Distance]
Out come:
EDIT:
You can turn of Row Subtotals to remove the totals
Thank for the quick answer! I will test it and implement it tomorrow (the real tables are much more complex and rather large - hundreds of millions entries) and I will lat you know.
EDIT: It works well, Thanks a lot!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |