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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors