Link data in Power BI

[jonasarnout]

Hi

My goal is to link schedule information from our schedule app to historic voyages from an Oracle database of an ERP system.

The schedule information needs to be enriched so that i can see the last voyage a ship has made with some additional information (trade, discharge port and departure time) which can be found in the historic data i have.

my oracle skills are somewhat limited so i pull all the voyages in from SYSDATE - 90 instead of only the last, hoping to fix it in PowerBI.

The unique combination would be: SHIP + VOYAGE NUMBER. The voyages numbering is year(4) + index(2), so the first voyage of every ship in 2018 would be 201801

I can only create a relation on ship between the two tables (1 to many) but i also need the voyage number to create a (1 to 1) link

I have managed to get the last voyage number with the this formula:

= CALCULATE(MAX('Voy Calc Req SN'[VOY]);FILTER('Voy Calc Req SN';[SHIP]='Schedule App Qry'[ship]))

Now i would also want to add the other info i need

I have tried this formula, that gives me an error ... I think because the fields are not numeric and cannot be calculated.

=CALCULATE('Voy Calc Req SN'[TRADE];
      FILTER('Voy Calc Req SN';[SHIP]='Schedule App Qry'[ship]
             && [VOY]='Schedule App Qry'[Last Voyage]))

Does anyone have a suggestion how to reach my goal?

if the easiest way to fix this is to finetune the query to begin with... here it is:

select va.SHIP_CODE ,va.SHIP ,va.VOY ,va.START_DATE ,va.END_DATE ,(select tr.TRADE_NAME from T022_TRADE tr where tr.TRADE_CODE = va.TRADE_CODE) as TRADE ,(select min(HAVN) from T580_POSITION where SKIPS_KODE = va.SHIP_CODE and VOYAGE_NO = va.VOY and ARR_DATETIME = (select MAX(ARR_DATETIME) from T580_POSITION where SKIPS_KODE = va.SHIP_CODE and VOYAGE_NO = va.VOY and PORTTYPE = 'D')) as Last_Discharge_Port ,to_char((select MAX(DEP_DATETIME) from T580_POSITION where SKIPS_KODE = va.SHIP_CODE and VOYAGE_NO = va.VOY and PORTTYPE = 'D'),'dd/mm/yyyy hh24:mi') as Last_Discharge_Departure from T600_VOYACC va where va.END_DATE > SYSDATE - 90 order by end_date asc

[v-huizhn-msft · Accepted solution]

Hi @jonasarnout,

If there is relationship between 'Voy Calc Req SN' and 'Schedule App Qry'? You create a measure rather than calculated column, right? If it is, please use the formula below and check if it works fine.

=
CALCULATE (
    FIRSTNONBLANK ( 'Voy Calc Req SN'[TRADE], 'Voy Calc Req SN'[TRADE] ),
    FILTER (
        'Voy Calc Req SN',
        [SHIP] = 'Schedule App Qry'[ship]
            && [VOY] = 'Schedule App Qry'[Last Voyage]
    )
)

Best Regards,
Angelia

[jonasarnout]

Hi Angelia

your formula works great!

I have used it to create a calculated column instead of a measure.

I would use a measure for operations with agregates.

the trade is text and will be used as a row filter hence a column seems more logical to me.

what is your motivation to suggest a measure instead of a calculated column in this case?