Forum Discussion
Calculating values based on independent table
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!