Converting PeopleSoft Query into DAX/Power BI

[MABI]

Hi Folks,

I need help in solving following issue.

I want to convert following PeopleSoft Query into Power BI.  

Note: PeopleSoft is being replaced by Power BI, data will be in Oracle 11 and we have Direct Query connection.

 ---- 

Select A.Field1,Field2,B.Field3Field4,C.Field5

FROM TABLE1 A, Table2 B, Table3 C

WHERE A.FIELID = B.FIELDID

   AND C.DATE1 =

       (SELECT MAX(C_ED.DATE1) FROM Table3 C_ED

       WHERE C.CODE = C_ED.CODE

       AND C_ED.DATE1 <= SYSDATE)

   AND C.CODE = A.CODE

   AND A.FIELDID ="FIELDID Entered in slicer"

  AND A.DATE2 > "DATE entered in filter"

This query is supposed to display a Table by selecting fields from 3 DB Tables, based on 2 entries FIELDID and "any DATE" --  plus following logic   

To explain the logic -->I have three tables Table1, Table2 and Table3

Table1 and Table2 has FIELDID and CODE columns, which are also creating relation between the tables.

All 3 tables have different dates of transactions.

I need to select a DATE from Table1, with following criteria:

Matching FIELDID from Table1 and Table2

For Max Date in Table C, where Code in table A = Code in Table B

and Date in Table C <= CURRENTDATE

and CODE in Table C = Code in Table A

Plus I also need to know what does ED. represents in C_ED.CODE or C_ED.DATE1

Appreciate help here

[v-zhenbw-msft]

Hi @MABI ,

Does your formula want to get a new table?

If yes, sorry for that Power BI cannot create a calculate table in Direct Query mode.

You can create a table when you switch into import mode.

Could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MABI]

Thanks for replying,

Two things - I do not want to create a table here and also cannot import data (data has 30+ tables, many with 40+ Million rows).

In the meantime, I have understood the logic of above PL/SQL and now my question has changed. So, new request is - how I can apply filters/conditions (like above) on multiple tables in Directquery or Hybrid connection type.

I was thinking of having a boolean Custom column with the results of these conditions and then applying filter on that, but I could not find a way to apply these conditions across multiple tables.

I have also changed my connection to Hybrid now by importing few DIm tables/column

Thanks again

[v-zhenbw-msft]

Hi @MABI ,

Sorry for that you just can create measure in Direct Query mode, the calculate column is not supported.

What visual do you want to display your results on? A table visual, a matrix table visual or some bar charts?

Could you please provide a mockup sample based on fake data?

Because it is difficult to reproduce your issue without a sample data.

And it will be helpful if you can show the desire result the expected result based on the sample data.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.