Hierarchal values without drill

[Anonymous]

input :

impact,  L1_Country ,L2_State  ,L3_City

100	IND
40	IND	KA
35	IND	TN
25	IND	MH
10	IND	KA	BLR
20	IND	KA	HSR
10	IND	KA	KLR

Output:

is this possible ?

[amitchandak]

@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 · Accepted solution]

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