Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |