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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors