Hi - hope you can help
I have two tables.
A fact table - Table 1
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]578908[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]578908[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]456782[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
</tbody>
A dim table - Table 2
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]20[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]30[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]40[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]70[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]60[/TD]
</tbody>
They are joined together using the Post Code Column.
I'm wanting to create a measure that will display as a new column in a table visual of the fact table. I want it to look like the below:
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]578908[/TD]
[TD="align: right"]30[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]578908[/TD]
[TD="align: right"]30[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]456782[/TD]
[TD="align: right"]40[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
[TD="align: right"]70[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
[TD="align: right"]70[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
[TD="align: right"]70[/TD]
</tbody>
This column (measure) shows the maximum (highest) mileage for any given Booking Number based on the Post Codes associated with that booking. As an example booking number 689053 has three post codes associated with it - the one with the highest miles is SE at 70 miles - so in the maximum miles column it says 70 for all of the 689053 booking numbers.
Please could you help me with a measure as I'm stumped.
I hope this makes sense.
Kind regards
Chris
I have two tables.
A fact table - Table 1
B | C | D | |
---|---|---|---|
Date | Booking Number | Post Code | |
GH | |||
DG | |||
PH | |||
DG | |||
PH | |||
SE |
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]578908[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]578908[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]456782[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
</tbody>
Sheet1
A dim table - Table 2
F | G | |
---|---|---|
Post Code | Miles | |
GH | ||
DG | ||
PH | ||
SE | ||
TS |
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]20[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]30[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]40[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]70[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]60[/TD]
</tbody>
Sheet1
They are joined together using the Post Code Column.
I'm wanting to create a measure that will display as a new column in a table visual of the fact table. I want it to look like the below:
I | J | K | L | |
---|---|---|---|---|
Date | Booking Number | Post Code | Maximum Miles | |
GH | ||||
DG | ||||
PH | ||||
DG | ||||
PH | ||||
SE |
<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]578908[/TD]
[TD="align: right"]30[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]578908[/TD]
[TD="align: right"]30[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]456782[/TD]
[TD="align: right"]40[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
[TD="align: right"]70[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
[TD="align: right"]70[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]02/03/2019[/TD]
[TD="align: right"]689053[/TD]
[TD="align: right"]70[/TD]
</tbody>
Sheet1
This column (measure) shows the maximum (highest) mileage for any given Booking Number based on the Post Codes associated with that booking. As an example booking number 689053 has three post codes associated with it - the one with the highest miles is SE at 70 miles - so in the maximum miles column it says 70 for all of the 689053 booking numbers.
Please could you help me with a measure as I'm stumped.
I hope this makes sense.
Kind regards
Chris