Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |