Referencing hierarchy in a different visual

[JanaP]

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 option to split it (now sure whether it will make any difference).

Say I have Product and Colour. I have some measures that rank Colour within Product in a matrix and I am using slicer with a hierarchy Product -> Colour. 

What need to see is a list of Products for each Colour (selected in a slicer) that show rank of the selected Colour for each Product. 

I have tried playing with slicers but the complain about having too many values where one would be expected. 

Creating a hiearchy Colour -> Product doesn't work either.  

Is there a Dax formula to reference the original matrix/ the row of the matrix? Or am I looking at it in a completely wrong way?

Thanks a lot! 

Jana

[JanaP]

Hello @v-yanjiang-msft,

Just wanto to say thank you again.

I didn't need to use this afterall as the table visual actually worked nicely with what I had. Then I had to move on to other painful measures, so sorry for the late reply!

Jana

[JanaP]

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 · Accepted solution]

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]

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]

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.

[JanaP]

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]

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.

[v-yanjiang-msft]

Hi @JanaP ,

According to your description, I tried to create a sample to reproduce your problem but failed, how can you rank color? I'm also not very clear about the expected result "a list of Products for each Colour (selected in a slicer) that show rank of the selected Colour for each Product. " Would you like to explain in more detail.

Best Regards,
Community Support Team _ kalyj