topic Urgent help- Dax in DAX Commands and Tips

Urgent help- Dax

Anonymous — Tue, 14 Dec 2021 09:34:07 GMT

Hey, Hope all of you are good.

Please find below data,

remark	reportdate	lob	geography	assetclass	sublob	Value(int)
lob	13-Dec-21	Markets				1
lgas	13-Dec-21	Markets	Domestic	Equity	A	2
lgas	13-Dec-21	Markets	Domestic	Equity	B	3
lgas	13-Dec-21	Markets	Domestic	Equity	C	4
lgas	13-Dec-21	Markets	Domestic	Equity	D	5
lgas	13-Dec-21	Markets	Domestic	Equity	E	6
lgas	13-Dec-21	Markets	Domestic	Equity	H	7
lgas	13-Dec-21	Markets	Domestic	Equity	F	8
lgas	13-Dec-21	Markets	Domestic	Equity	G	9
lgas	13-Dec-21	Markets	International	Equity	B	1
lgas	13-Dec-21	Markets	Domestic	Currency	F	2
lgas	13-Dec-21	Markets	Domestic	Currency	D	3
lgas	13-Dec-21	Markets	International	Currency	D	4
lgas	13-Dec-21	Markets	International	Equity	D	5
lga	13-Dec-21	Markets	Domestic	Currency		6
lga	13-Dec-21	Markets	Domestic	Equity		7
lga	13-Dec-21	Markets	International	Equity		8
lga	13-Dec-21	Markets	International	Currency		9
lg	13-Dec-21	Markets	International			1
lg	13-Dec-21	Markets	Domestic			2

====>>>>Wrong Output coming,

hierarchy of matrix shown below,

I need output in hierarchy wise below, Kindly help asap.

Please Help MVP of Power Bi.....Super heroes of power bi

Re: Urgent help- Dax

amitchandak — Tue, 14 Dec 2021 09:56:39 GMT

Anonymous , Based on what I got

There are few solutions discussed on this issue, see if those can help

https://community.powerbi.com/t5/Custom-Visuals-Development/Remove-empty-hirearchy-levels/m-p/1091582

Re: Urgent help- Dax

Samarth_18 — Tue, 14 Dec 2021 10:05:09 GMT

Hi Anonymous ,

To achive this, you can follow below steps:-

1. Right click on lob column and select create a hierarchy:-

2. Now you will see new hierarchy created

3. Now right click on geograph column and click on add to hierarchy and add it lob hierarchy:-

4.Follow the same step for asset class and sub lob. You will see below output

5. Now add this hierachy into the matrix and put filter for non blank value in assetclass and sublob column

Thanks,

Samarth

Re: Urgent help- Dax

Anonymous — Tue, 14 Dec 2021 10:58:58 GMT

Thanks, For your valuable time. Actually if you see my desire output . I required value as it is in total. In your solution I got value of category as subtotal(sum). Which is not correct solution. Kindly help. Not getting desired output from 5 day. Samarth_18 and amitchandak .

Re: Urgent help- Dax

Anonymous — Wed, 15 Dec 2021 02:50:28 GMT

No solution with you? Power bi Superheroes????? 😐

Re: Urgent help- Dax

smpa01 — Wed, 15 Dec 2021 03:07:57 GMT

Anonymous

How-to-Get-Your-Question-Answered-Quickly

How-to-provide-sample-data-in-the-Power-BI-Forum

Re: Urgent help- Dax

v-angzheng-msft — Thu, 30 Dec 2021 05:05:52 GMT

Hi, Anonymous

Try to create a measure like this:

Measure = var _t1=CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK()) var _t2=CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK()&&'Table'[assetclass]=BLANK()) var _t3=CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK()&&'Table'[assetclass]=BLANK()&&'Table'[geography]=BLANK()) var _t4=0 //whatever you want // CALCULATE(SUM('Table'[Value(int)]),'Table'[sublob]=BLANK()&&'Table'[assetclass]=BLANK()&&'Table'[geography]=BLANK()&&'Table'[lob]=BLANK()) var _if=IF( ISINSCOPE( 'Table'[lob] ), IF( ISINSCOPE( 'Table'[geography] ), IF( ISINSCOPE( 'Table'[assetclass] ), IF( ISINSCOPE( 'Table'[sublob] ), SUM('Table'[Value(int)])// 0 , _t1// 1 ), _t2// 2 ), _t3// 3 ), _t4// 4 ) return _if

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Urgent help- Dax

Anonymous — Thu, 30 Dec 2021 06:09:34 GMT

Thanks for detail replies