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

Urgent- Dax requirement for desired output

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

 

2 ACCEPTED SOLUTIONS
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

The following output is what you want.

vluwangmsft_0-1639721211458.png

Base on table ,create a measure:

measure=
IF (
    HASONEVALUE ( 'Table'[sublob] ),
    MAX ( 'Table'[Value(int)] ),
    IF (
        HASONEVALUE ( 'Table'[assetclass] ),
        CALCULATE (
            MAX ( 'Table'[Value(int)] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[lob] = MAX ( 'Table'[lob] )
                    && 'Table'[geography] = MAX ( 'Table'[geography] )
                    && 'Table'[assetclass] = MAX ( 'Table'[assetclass] )
                    && 'Table'[sublob] = BLANK ()
            )
        ),
        IF (
            HASONEVALUE ( 'Table'[geography] ),
            CALCULATE (
                MAX ( 'Table'[Value(int)] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[lob] = MAX ( 'Table'[lob] )
                        && 'Table'[geography] = MAX ( 'Table'[geography] )
                        && 'Table'[assetclass] = BLANK ()
                )
            ),
            IF (
                HASONEVALUE ( 'Table'[lob] ),
                CALCULATE (
                    MAX ( 'Table'[Value(int)] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[lob] = MAX ( 'Table'[lob] )
                            && 'Table'[geography] = BLANK ()
                    )
                ),
                CALCULATE (
                    MAX ( 'Table'[Value(int)] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[lob] = BLANK () )
                )
            )
        )
    )
)

Then use the measure create visual ,and set filter is not empty:

vluwangmsft_1-1639721315148.png

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

View solution in original post

wdx223_Daniel
Super User
Super User

7 REPLIES 7
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1639724455376.png

 

Anonymous
Not applicable

Working perfect but Market showing wrong value

Anonymous
Not applicable

Working perfect but Market showing wrong value

v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

The following output is what you want.

vluwangmsft_0-1639721211458.png

Base on table ,create a measure:

measure=
IF (
    HASONEVALUE ( 'Table'[sublob] ),
    MAX ( 'Table'[Value(int)] ),
    IF (
        HASONEVALUE ( 'Table'[assetclass] ),
        CALCULATE (
            MAX ( 'Table'[Value(int)] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[lob] = MAX ( 'Table'[lob] )
                    && 'Table'[geography] = MAX ( 'Table'[geography] )
                    && 'Table'[assetclass] = MAX ( 'Table'[assetclass] )
                    && 'Table'[sublob] = BLANK ()
            )
        ),
        IF (
            HASONEVALUE ( 'Table'[geography] ),
            CALCULATE (
                MAX ( 'Table'[Value(int)] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[lob] = MAX ( 'Table'[lob] )
                        && 'Table'[geography] = MAX ( 'Table'[geography] )
                        && 'Table'[assetclass] = BLANK ()
                )
            ),
            IF (
                HASONEVALUE ( 'Table'[lob] ),
                CALCULATE (
                    MAX ( 'Table'[Value(int)] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[lob] = MAX ( 'Table'[lob] )
                            && 'Table'[geography] = BLANK ()
                    )
                ),
                CALCULATE (
                    MAX ( 'Table'[Value(int)] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[lob] = BLANK () )
                )
            )
        )
    )
)

Then use the measure create visual ,and set filter is not empty:

vluwangmsft_1-1639721315148.png

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

Anonymous
Not applicable

Thanks working perfect.

Anonymous
Not applicable

Thanks working perfect.

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
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.

Top Solution Authors