Forum Discussion

JanaP's avatar
JanaP
Icon for Helper I rankHelper I
4 years ago
Solved

Referencing hierarchy in a different visual

Hello all, I keep sctratching my head with this task.   I have two-level hierarchy, both levels have several hundreds of elements. All data is coming from the same table, but there will be an opti...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi JanaP ,

    In my sample, Rank is a calculated column and Rank measure is a measure.

    However, I tried to create a measure like yours to rank the color, it get the incorrect result. I'm not clear why you use ISINSCOPE, I remove it and modify the formula, then get the correct result.

    In the Rank measure, no matter put the measure or calculated column, all return the correct result.


    Note: You can download my pbix attached to figure it out.

     

    Best Regards,
    Community Support Team _ kalyj

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

JanaP's avatar
JanaP
Icon for Helper I rankHelper I
4 years ago

Hi v-yanjiang-msft 

 

Thank you again for taking time to help me. 

 

I tried to implement the steps above, however I have not been successful. 

 

I think the problem lies in the acquiring the rank in the first place. How did yo get yours? Was it calculated column or a measure? 

When I treid to implement your Rank into a measure it returned this error: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

This is the measure I used to get my ranking: 

Rank = 
    IF(
        ISINSCOPE('Hierarchy Question'[Colour]),  
            RANKX(
                FILTER(
                    ALL(
                        'Hierarchy Question'[Product],
                        'Hierarchy Question'[Colour]
                        ),
                        'Hierarchy Question'[Product] = MAX('Hierarchy Question'[Product])
                    ),
                CALCULATE('Hierarchy Question'[Total No of Sales]) 
                )
            )

 

And this is what my matrix looks like (BTW I find it very off putting that I can't upload a sample file):

 

Please note that the ranking within one product is completely independent from any other product.

 

With my Rank I followed the rest of your steps and all was fine until putting the Rank Measure in the table - it returns 1 for everything. So, there is a connectiong problem between the Rank (however obtained) and the dispaly of it in another visual. 

 

Is there something obvious I am missing? 

 

I have already taken some ideas from your sample, so that's really great.

 

Thanks again!

 

Jana

 

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi JanaP ,

In my sample, Rank is a calculated column and Rank measure is a measure.

However, I tried to create a measure like yours to rank the color, it get the incorrect result. I'm not clear why you use ISINSCOPE, I remove it and modify the formula, then get the correct result.

In the Rank measure, no matter put the measure or calculated column, all return the correct result.


Note: You can download my pbix attached to figure it out.

 

Best Regards,
Community Support Team _ kalyj

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

  • JanaP's avatar
    JanaP
    Icon for Helper I rankHelper I
    4 years ago

    Thanks v-yanjiang-msft 

     

    I greatly appreciate your time. This works fine with simplified example I gave you. 👏

     

    In reality, I have a problem to produce the correct ranking at the moment. My Product and Colour are now in different tables. These two tables are related by a different column (say Size).

    Table 1: Product

    Columns: Product, Size  (1 side)

    Table 2: Colour

    Columns: Colour, Size (many side), some other columns used in the measure by which the ranking is (say Sales)

     

    I am having trouble to tell PBI to use columns Product and Colour in the table part of the RANKX formula. I have tried RELATED but it throws an error saying that there is not a relationship. When they were in the same table all worked fine, but now it is not an option.

     

    Could you please enlighten me, how to do this?

     

    Thanks a lot 🌞

     

    Jana

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi JanaP ,

      According to your description, I create a new sample.

      Product table:

      Colour table:

      They are related by the Size column.

      To create the hierarchy of Product and colour, the same product has the same size in my sample, otherwise the product and colour has no logical relationship.

      Then create a seperate table with products, and don't make relationship with other tables.

      Create two measures.

       

      Measure =
IF (
    MAX ( 'Colour'[Sales] ) = BLANK (),
    BLANK (),
    RANKX (
        FILTER ( ALL ( 'Colour' ), 'Colour'[Size] = MAX ( 'Colour'[Size] ) ),
        CALCULATE ( SUM ( 'Colour'[Sales] ) )
    )
)

      Rank Measure =
CALCULATE (
    [Measure],
    FILTER ( ALL ( 'Product' ), 'Product'[Product] = MAX ( 'Table'[Product] ) ),
    FILTER ( ALL ( 'Colour' ), 'Colour'[Colour] = SELECTEDVALUE ( Colour[Colour] ) )
)

       

       Get the result.

      I attach my sample below for reference.

       

      Best Regards,
      Community Support Team _ kalyj

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