Urgent- Dax requirement for desired output

[Anonymous]

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,

1)

2)

3)

4)

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

1)

2)

3)

4)

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

The following output is what you want.

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:

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

Best Regards

Lucien

[wdx223_Daniel · Accepted solution]

[Anonymous]

Working perfect but Market showing wrong value

[Anonymous]

Working perfect but Market showing wrong value

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

The following output is what you want.

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:

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

Best Regards

Lucien

[Anonymous]

Thanks working perfect.

[Anonymous]

Thanks working perfect.

[amitchandak]

@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