cancel
Showing results for
Did you mean:
Helper III

## Hierarchy value when missing value

Hello I have this measure below

it gives me this output.  As you can see North Sydney Strorage below is giving me BLANK.

What I would like is for any blank values as such, I would like to capture the value of the above hierarchy, in this case the value 1.00 from 343-Nova Scotia North.

am I providing all the details needed for you to help me?

1 ACCEPTED SOLUTION
Community Support

Hi, @salman_ali

You may try the following measure to see if it helps.

``````Result =
IF (
ISINSCOPE ( 'Table'[Level4] ),
SUM ( 'Table'[Val] ),
IF (
ISINSCOPE ( 'Table'[Level3] ),
1,
IF (
ISINSCOPE ( 'Table'[Level2] )
&& MAX ( 'Table'[Level2] ) = "AT1",
1.16,
IF ( ISINSCOPE ( 'Table'[Level1] ) && MAX ( 'Table'[Level1] ) = "EST", 0.68 )
)
)
)``````

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi, @salman_ali

Based on your description, I assume that you want to do different calculations for different level. I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

``````Result =
IF(
ISINSCOPE('Table'[Level4]),
SUM('Table'[Val]),
IF(
ISINSCOPE('Table'[Level3])&&MAX('Table'[Level3])="343-Nova Scotia North",
1,
IF(
ISINSCOPE('Table'[Level2])&&MAX('Table'[Level2])="AT1",
1.16,
IF(
ISINSCOPE('Table'[Level1])&&MAX('Table'[Level1])="EST",
0.68
)
)
)
)``````

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper III

@v-alq-msft  Hi I see that your measure will be able to work, however is it possible for you to give me a more generic measure as opposed to have "343-Nova Scotia North" in measure? since I am working with varios other markets other than "343-Nova Scotia North" as well

Community Support

Hi, @salman_ali

You may try the following measure to see if it helps.

``````Result =
IF (
ISINSCOPE ( 'Table'[Level4] ),
SUM ( 'Table'[Val] ),
IF (
ISINSCOPE ( 'Table'[Level3] ),
1,
IF (
ISINSCOPE ( 'Table'[Level2] )
&& MAX ( 'Table'[Level2] ) = "AT1",
1.16,
IF ( ISINSCOPE ( 'Table'[Level1] ) && MAX ( 'Table'[Level1] ) = "EST", 0.68 )
)
)
)``````

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!