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 for taking time and trying to help me.

 

The colour is ranked by using a measure, which calculates it for example by number of sales, by amount of sales or a different criterion.

 

This is an example of my data shown in excel, in PBI Product and Colour are in a hierarchy where the product is on the higher level, the PBI visual I am working with is a matrix. 

 

 

Here are 3 examples of what I want to produce (the user chooses a Colour in a slicer and the visual shows ranks for that Colour across different Products):

 

In order to rank Colour within one Product I need a hierarchy, which is fine.

 

I was using a slicer with a hierarchy to produce the desired visual but it didn't work (came up with a context error) and I assume this is due to using ISINSCOPE function in the ranking measure.

I abandoned the hierarchy in the slicer and now I am using one slicer for Product and one for Colour while I change the hierarchy in the matrix (i.e. the order of Product and Colour in row). That gives me the correct result, however, I'd like to see the list of Products and Rank without the Colour in the same visual. Removing it from the visual breaks down the hierarchy, so that is not an option.  

 

Any ideas are greatly appreciated.

 

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 sample. Not sure if I fully understand.

    1. Create a rank column.

    Rank =
RANKX (
    FILTER ( 'Table', 'Table'[Product] = EARLIER ( 'Table'[Product] ) ),
    'Table'[Sales],
    ,
    DESC,
    DENSE
)

    2.Create a new product table, don't make relationship between the two tables.

    Product = VALUES('Table'[Product])

    3.Create a measure.

    Rank measure =
MAXX (
    FILTER (
        'Table',
        'Table'[Product] = MAX ( 'Product'[Product] )
            && 'Table'[Colour] = SELECTEDVALUE ( 'Table'[Colour] )
    ),
    'Table'[Rank]
)

    Put Product column from the new table and the measure in a visual, and checked the "Show item with no data", 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.