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.
Hello.
A have a one problem in SQL i now how to solve the case, bat in power bi i a new.
I hawe a sql statment how to this do in power bi:
WITH TEMP (PARENT_ID, ID, i) AS -- tempory table
(
SELECT
PARENT_ID,
ID,
1
FROM
DW.ORGANIZACIJA -- table on the base
WHERE
PROGENOT IN (SELECT DISTINCT PROGENOT FROM DW.PAKIRANO) -- table on the base
UNION ALL
SELECT
A.PARENT_ID,
A.ID,
i + 1
FROM
DW.ORGANIZACIJA A, -- table on the base
TEMP T
WHERE
T.PARENT_ID=A.ID
AND A.PARENT_ID IS NOT NULL
AND i < 1000
)
SELECT
DISTINCT
'SISTEMIMPOL' CONCAT ORG1.PROGENOT as parent_id,
ORG1.ORGENOTA AS PARENT,
'SISTEMIMPOL' CONCAT ORG2.PROGENOT as child_id,
ORG2.ORGENOTA AS CHILD,
TEMP.PARENT_ID AS RAZVRSTI_PARENT_ID,
TEMP.ID AS RAZVRSTI_CHILD_ID
FROM
TEMP
INNER JOIN DW.ORGANIZACIJA AS ORG1 ON TEMP.PARENT_ID=ORG1.ID
INNER JOIN DW.ORGANIZACIJA AS ORG2 ON TEMP.ID=ORG2.ID
ORDER BY
RAZVRSTI_PARENT_ID,
RAZVRSTI_CHILD_ID
;
Thanks.
Lea
Solved! Go to Solution.
Hi @LeaRupnik ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. More people will benefit from here. Thanks in advance.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @LeaRupnik ,
If you want to import the data, you could try add the code into "SQL statement" while connecting SQL Server.
If you want to create a new table, you could try the function of SUMMARIZE. Here are some blogs that you can learn from.
best practices using summarize and addcolumns
Joining tables in Power BI with Power Query and DAX
side effects in dax of the sort by column setting
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello.
i woud like to do tempory table in PowerBi. At first i greate tem table
Temp1 = DISTINCT(SELECTCOLUMNS(ORGANIZACIJA;"PARENT_ID";ORGANIZACIJA[PARENT_ID];"ID";ORGANIZACIJA[ID];"i";1))
PROGENOT IN (SELECT DISTINCT PROGENOT FROM DW.PAKIRANO)
Thanks.
LP
LEa
Hi @LeaRupnik ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. More people will benefit from here. Thanks in advance.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @LeaRupnik ,
Sorry for late back. You could use CALCULATETABLE function or FILTER function to add filter conditions like "where" in SQL.
Table 2 = CALCULATETABLE (VALUES('Table'[Country]),'Table'[Product] = "Paseo" )
Reference:
https://www.sqlbi.com/articles/filter-vs-calculatetable-optimization-using-cardinality-estimation/
Best Regards,
Xue Ding
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |