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
Anonymous
Not applicable

Hierarchal values without drill

input :

impact,  L1_Country ,L2_State  ,L3_City

100IND  
40INDKA 
35INDTN 
25INDMH 
10INDKABLR
20INDKAHSR
10INDKAKLR

 

Output:

jonnalav_0-1627660664775.png

is this possible ?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @amitchandak 

Thanks for your response. this idea worked for me.  I made few changes to your formula to restrict blanks at each level. 

 

values = VAR L1= CALCULATE (sum(main[impact]),ISBLANK(main[L2_State])=TRUE)
VAR L2= calculate(sum(main[impact]), userelationship(dim[L1_Country], main[L2_State]),ISBLANK(main[L3_City])=TRUE)
VAR L3= calculate(sum(main[impact]), userelationship(dim[L1_Country], main[L3_City]))
RETURN L1+L2+L3

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , Create a new table

Dim = filter(distinct(union(distinct(Table[L1_Country]), distinct(Table[L2_State]), distinct(Table[L3_City]))),[L1_Country] <> blank())

 

Join this table (only column- L1_Country) with all three columns

Assume L1_Country to L1_Country is active and others are inactive 

 

Then create a meausre like this and use

sum(Table[impact]) + calculate(sum(Table[impact]), userelationship(dim[L1_Country], Table[L2_State]))

+ calculate(sum(Table[impact]), userelationship(dim[L1_Country], Table[L3_City]))

 

refer if needed

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Anonymous
Not applicable

Hi @amitchandak 

Thanks for your response. this idea worked for me.  I made few changes to your formula to restrict blanks at each level. 

 

values = VAR L1= CALCULATE (sum(main[impact]),ISBLANK(main[L2_State])=TRUE)
VAR L2= calculate(sum(main[impact]), userelationship(dim[L1_Country], main[L2_State]),ISBLANK(main[L3_City])=TRUE)
VAR L3= calculate(sum(main[impact]), userelationship(dim[L1_Country], main[L3_City]))
RETURN L1+L2+L3

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.