Parent-Child hiearchy sql convert

[Anonymous]

hej

i got this sql scrip that i need to use in power bi, but i just cant get it to work in dax

UPDATE b SET
[Parent] = ISNULL
(
(
SELECT
MAX(a.[LedgerAccount])
FROM
[dbo].[DIM_LedgerAccounts] a
WHERE
a.[LedgerAccount] < b.[LedgerAccount]
AND a.[Indentation] = b.[Indentation] - 1
AND a.[IncomeBalanceType] = b.[IncomeBalanceType]
AND a.[Company] = b.[Company]
)
,b.[LedgerAccount]
)
FROM
[dbo].[DIM_LedgerAccounts] b
GO

can someone help me make a solution

[v-chuncz-msft]

@Anonymous,

Try using ISBLANK, MAXX and FILTER to add a calculated column.

https://community.powerbi.com/t5/Desktop/conditional-join/m-p/340423#M152404

[Anonymous]

Hej

so i made this dax from your input

but it doesnt return an error but just cells with no value

i made the column parent in d_ledger account to be equel = 1 because there never is a parent in this data.

parent test = IF(('d_ledger account'[Parent])="1";MAXX(

FILTER(

'd_ledger account';

'd_ledger account'[No_] >= RELATED('parent test'[No_])

&& 'd_ledger account'[Indentation] = RELATED('parent test'[Indentation]) - 1

&& 'd_ledger account'[Income_Balance] = RELATED('parent test'[Income_Balance])

);

'd_ledger account'[No_]

);"TEST")

can you help me with some more feedback?

[v-chuncz-msft · Accepted solution]

@Anonymous,

Check the post below.

https://community.powerbi.com/t5/Desktop/This-Function/m-p/326489#M145679