Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

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

     

    If it doesn't work, please show us some sample data with OneDrive for business. Do mask sensitive data before uploading.

     

    Best Regards

    Allan

     

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

v-alq-msft's avatar
v-alq-msft
Community Support
5 years ago

Hi, Anonymous 

 

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:

 

For further information about 'isinscope', please refer to the following document.

isinscope 

 

Best Regards

Allan

 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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 

    • v-alq-msft's avatar
      v-alq-msft
      Community Support
      5 years ago

      Hi, Anonymous 

       

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

       

      If it doesn't work, please show us some sample data with OneDrive for business. Do mask sensitive data before uploading.

       

      Best Regards

      Allan

       

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