Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Solved! Go to 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
Working perfect but Market showing wrong value
Working perfect but Market showing wrong value
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
Thanks working perfect.
Thanks working perfect.
@Anonymous , Based on what I got.
There are few solutions discussed on this issue, see if those can help
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |