Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VO
Helper I
Helper I

Hierarchy Sales for Entire Path

How can one get the entire sales of the enitire hierarchy, from table1.1 and table1.2. Desired output is table1.3 (maybe using PATHCONTAINS). 

Additional columns in the model: Path, Path Length

Path=PATH([Client],[Referral])

Path Length=PATHLENGTH([Path])

Hierarchy sales.JPG

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

What you have here is a Transitive Closure problem. https://community.powerbi.com/t5/Quick-Measures-Gallery/Transitive-Closure/td-p/783828

 

I have attached the PBIX but you can solve this with two columns:

 

Path = 
    VAR __table1 = FILTER('Table1 1',[Client] = EARLIER([Client]))
    VAR __table1a = DISTINCT(SELECTCOLUMNS(__table1,"__to",[Referral]))
    VAR __table2 = FILTER(ALL('Table1 1'),[Client] IN __table1a)
    VAR __table2a = DISTINCT(SELECTCOLUMNS(__table2,"__to",[Referral]))
    VAR __table3 = FILTER(ALL('Table1 1'),[Client] IN __table2a)
    VAR __table3a = DISTINCT(SELECTCOLUMNS(__table3,"__to",[Referral]))
    VAR __table4 = FILTER(ALL('Table1 1'),[Client] IN __table3a)
    VAR __table4a = DISTINCT(SELECTCOLUMNS(__table4,"__to",[Referral]))
RETURN 
//    CONCATENATEX(__table1,[Referral],"|")
CONCATENATEX(DISTINCT(UNION(__table1a,__table2a,__table3a,__table4a)),[__to],"|")

Sales = 
    VAR __Sales1 = LOOKUPVALUE('Table1 2'[Sales],'Table1 2'[Client],[Client])
    VAR __Sales2 = 'Table1 1'
    VAR __Sales2a = ADDCOLUMNS(__Sales2,"__Include",PATHCONTAINS([Path],EARLIER([Client])))
    VAR __Sales2b = ADDCOLUMNS(__Sales2a,"__Sales",IF([__Include],LOOKUPVALUE('Table1 2'[Sales],'Table1 2'[Client],[Client])))
RETURN
    __Sales1 + SUMX(__Sales2b,[__Sales])

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

What you have here is a Transitive Closure problem. https://community.powerbi.com/t5/Quick-Measures-Gallery/Transitive-Closure/td-p/783828

 

I have attached the PBIX but you can solve this with two columns:

 

Path = 
    VAR __table1 = FILTER('Table1 1',[Client] = EARLIER([Client]))
    VAR __table1a = DISTINCT(SELECTCOLUMNS(__table1,"__to",[Referral]))
    VAR __table2 = FILTER(ALL('Table1 1'),[Client] IN __table1a)
    VAR __table2a = DISTINCT(SELECTCOLUMNS(__table2,"__to",[Referral]))
    VAR __table3 = FILTER(ALL('Table1 1'),[Client] IN __table2a)
    VAR __table3a = DISTINCT(SELECTCOLUMNS(__table3,"__to",[Referral]))
    VAR __table4 = FILTER(ALL('Table1 1'),[Client] IN __table3a)
    VAR __table4a = DISTINCT(SELECTCOLUMNS(__table4,"__to",[Referral]))
RETURN 
//    CONCATENATEX(__table1,[Referral],"|")
CONCATENATEX(DISTINCT(UNION(__table1a,__table2a,__table3a,__table4a)),[__to],"|")

Sales = 
    VAR __Sales1 = LOOKUPVALUE('Table1 2'[Sales],'Table1 2'[Client],[Client])
    VAR __Sales2 = 'Table1 1'
    VAR __Sales2a = ADDCOLUMNS(__Sales2,"__Include",PATHCONTAINS([Path],EARLIER([Client])))
    VAR __Sales2b = ADDCOLUMNS(__Sales2a,"__Sales",IF([__Include],LOOKUPVALUE('Table1 2'[Sales],'Table1 2'[Client],[Client])))
RETURN
    __Sales1 + SUMX(__Sales2b,[__Sales])

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you Greg. It is working. Kindly let me spend more time to understand how it works.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors