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.
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])
Solved! Go to Solution.
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])
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])
Thank you Greg. It is working. Kindly let me spend more time to understand how it works.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |