Customer hierarchy parent/child

[jhowe1]

I'm struggling to get my head around this in power bi. I have a data warehouse with DimCustomer, FactSalesInvoice, FactCustomerInvoiceAssociationHistoric (which allocates invoices to levels that are not allocated against in the system). How can I get the associated level totals to show correctly in a matrix? The levels are created in dimcustomer using self joins in a cte, i'm hoping to do as much in SQL as possible (in dim customer view) to give Power BI the data in the format it needs, rather than having to modify multiple measures...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--ALTER VIEW [pbi].[DimCustomer]
--AS
    WITH
        ParentCustomers
        AS
        (
            SELECT CustomerID
            , ParentCustomerID
            , CustomerName
            FROM dbo.DimCustomer
            GROUP BY CustomerID, ParentCustomerID, CustomerName
        )
        ,
        L3Customers
        AS
        (
            SELECT CustomerID 
                , ParentCustomerID
                , CustomerName
            FROM dbo.DimCustomer
            GROUP BY CustomerID, ParentCustomerID, CustomerName
        )
        ,
        L4Customers
        AS
        (
            SELECT CustomerID
                , ParentCustomerID
                , CustomerName
            FROM dbo.DimCustomer
            GROUP BY CustomerID, ParentCustomerID, CustomerName
        )
        ,
        L5Customers
        AS
        (
            SELECT CustomerID
                , CustomerName
            FROM dbo.DimCustomer
            GROUP BY CustomerID, CustomerName
        )
    SELECT L1.CustomerRecID                                 AS 'KEY_Customer'
     , L1.CustomerID                                        AS 'Customer ID'
     , L1.CustomerName                                      AS 'Customer Name'
     , ISNULL(L2.CustomerName, 'N/A')                       AS 'Parent Customer'
     , ISNULL(L3.CustomerName, 'N/A')                       AS 'L3 Customer Name'
     , ISNULL(L4.CustomerName, 'N/A')                       AS 'L4 Customer Name'
     , ISNULL(L5.CustomerName, 'N/A')                       AS 'L5 Customer Name'
    FROM dbo.DimCustomer AS L1
        OUTER APPLY (SELECT TOP 1 * FROM ParentCustomers AS L2 WHERE L1.ParentCustomerID = L2.CustomerID) AS L2
        OUTER APPLY (SELECT TOP 1 * FROM L3Customers AS L3 WHERE L2.ParentCustomerID = L3.CustomerID) AS L3
        OUTER APPLY (SELECT TOP 1 * FROM L4Customers AS L4 WHERE L3.ParentCustomerID = L4.CustomerID) AS L4
        OUTER APPLY (SELECT TOP 1 * FROM L5Customers AS L5 WHERE L4.ParentCustomerID = L5.CustomerID) AS L5

GO

[amitchandak]

@jhowe1 , Please refer to these two can help, in case you have not tried

https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-ch...

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[jhowe1]

Thanks for your reply, I'm hoping to find a solution where most of the work can be done in SQL, as we have multiple measures so don't want to have to modify them unless absolutely necessary... 

@amitchandak I'm trying to use DAX Path as in https://www.daxpatterns.com/parent-child-hierarchies/ and one of your examples

Path = PATH(Customer[Customer ID], Customer[Parent Customer ID]) in my customer dimension however i cannot get it to work. I believe this to be because customer dimension SCD type 2 effectively contains duplicates inactive/active records. If I filter my dimension by Current = TRUE it works. However I cannot leave this filter on. I tried 

Path = IF (Customer[Current] = TRUE, PATH(Customer[Customer ID], Customer[Parent Customer ID]))

however still doesn't work. How do I get this to work?