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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Urgent help- Dax

Hey, Hope all of you are good.

 

Please find below data,

 

remarkreportdatelobgeographyassetclasssublobValue(int)
lob13-Dec-21Markets   1
lgas13-Dec-21MarketsDomesticEquityA2
lgas13-Dec-21MarketsDomesticEquityB3
lgas13-Dec-21MarketsDomesticEquityC4
lgas13-Dec-21MarketsDomesticEquityD5
lgas13-Dec-21MarketsDomesticEquityE6
lgas13-Dec-21MarketsDomesticEquityH7
lgas13-Dec-21MarketsDomesticEquityF8
lgas13-Dec-21MarketsDomesticEquityG9
lgas13-Dec-21MarketsInternationalEquityB1
lgas13-Dec-21MarketsDomesticCurrencyF2
lgas13-Dec-21MarketsDomesticCurrencyD3
lgas13-Dec-21MarketsInternationalCurrencyD4
lgas13-Dec-21MarketsInternationalEquityD5
lga13-Dec-21MarketsDomesticCurrency 6
lga13-Dec-21MarketsDomesticEquity 7
lga13-Dec-21MarketsInternationalEquity 8
lga13-Dec-21MarketsInternationalCurrency 9
lg13-Dec-21MarketsInternational  1
lg13-Dec-21MarketsDomestic  2

 

 

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

hierarchy of matrix shown below,

 

SankeyThakkar_7_1-1639472455651.png

 

1)

SankeyThakkar_7_0-1639472332825.png

 

2)

SankeyThakkar_7_2-1639472556813.png

 

3)

 

SankeyThakkar_7_3-1639472598428.png

 

4)

 

SankeyThakkar_7_4-1639472652999.png

 

SankeyThakkar_7_5-1639472671405.png

 

 

 

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

 

1)

 

SankeyThakkar_7_6-1639472851072.png

 

2)

 

SankeyThakkar_7_7-1639472951862.png

3)

 

SankeyThakkar_7_8-1639473723352.png

 

4)

SankeyThakkar_7_9-1639473965167.png

 

 

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

 

@amitchandak 

@AlexisOlson 

@v-zhangti 

@Anand24 

@v-yalanwu-msft 

 

 

 

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1640840711673.png

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.

View solution in original post

7 REPLIES 7
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1640840711673.png

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.

Anonymous
Not applicable

Thanks for detail replies

 

Anonymous
Not applicable

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

@Anonymous 

 

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

 

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Samarth_18
Community Champion
Community Champion

Hi @Anonymous ,

To achive this, you can follow below steps:-

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

Samarth_18_0-1639475699793.png

2. Now you will see new hierarchy created

Samarth_18_1-1639475763546.png

 

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

Samarth_18_2-1639475889964.png

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

Samarth_18_3-1639475971727.png

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

Samarth_18_4-1639476281616.png

Thanks,

Samarth

 

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Anonymous
Not applicable

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 .

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors