Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filter a ranked column to return a single row with ranking before the filter applied

Hi All, 

Wonder if someone can help me. 

I have the following ranking measure:

 

Rank Units =
IF (
    HASONEVALUE ( 'Segment'[Segment] ),
    RANKX (
        ALLSELECTED ( 'Company'[Company] ),
        'Analysis Measures'[Total Units],
        ,
        DESC,
        DENSE
    ),
    BLANK ()
)

 

Which works very and allows me to make a table like below:

 

ranking Table.png

 

 

 

 

 

 

 

 

I need help with 2 things...

1) I want to remove "Others" from the ranking - This can be filtered out before the ranking done. 

2) I want to create a measure which just returns the ranking in this table when Company="HP inc" so in this case "3", but must still work with the filters applied to the page report. 

 

Thank you

 

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

@Anonymous 

The code above works fine. The only problem is that you do not have a filter on 'Segment'[Segment] but rather on 'Segment'[Lenovo_Segment]. So, since we are checking whether HASONEVALUE ( 'Segment'[Segment] ) it always returns blank.  So you can either:

a) Include the proper filter on 'Segment'[Segment]

b) Change the code to do the check on 'Segment'[Lenovo_Segment] instead:

 

Rank Units V2 =
IF (
    HASONEVALUE ( 'Segment'[Lenovo_Segment] ),
    RANKX (
        FILTER ( ALL ( 'Company'[Company] ), 'Company'[Company] <> "Others" ),
        'Analysis Measures'[Total Units],
        ,
        DESC,
        DENSE
    ),
    BLANK ()
)

 

 

c) Other options like getting rid of that check altogether.

 

Finally, do note that you are not filtering out "Others" on the visual table, although you are using it as reference

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

 

 

View solution in original post

AlB
Super User
Super User

@Anonymous 

I dont' get a 4, I get a 3, so I'm not sure what you're doing. Be sure to apply a filter to the table visual excluding "Others" (or to the whole page) if you're to compare its results to those of the card.

Make sure you apply the geo filter to both table and card visuals; best use a slicer on Geography'[GEO] so that it applies to the whole page.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

10 REPLIES 10
AlB
Super User
Super User

@Anonymous 

I dont' get a 4, I get a 3, so I'm not sure what you're doing. Be sure to apply a filter to the table visual excluding "Others" (or to the whole page) if you're to compare its results to those of the card.

Make sure you apply the geo filter to both table and card visuals; best use a slicer on Geography'[GEO] so that it applies to the whole page.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Anonymous
Not applicable

OK, I understand where I was going wrong. 

Thank you so much for your help.... Sorry I am just learning DAX... 

AlB
Super User
Super User

@Anonymous 

The code above works fine. The only problem is that you do not have a filter on 'Segment'[Segment] but rather on 'Segment'[Lenovo_Segment]. So, since we are checking whether HASONEVALUE ( 'Segment'[Segment] ) it always returns blank.  So you can either:

a) Include the proper filter on 'Segment'[Segment]

b) Change the code to do the check on 'Segment'[Lenovo_Segment] instead:

 

Rank Units V2 =
IF (
    HASONEVALUE ( 'Segment'[Lenovo_Segment] ),
    RANKX (
        FILTER ( ALL ( 'Company'[Company] ), 'Company'[Company] <> "Others" ),
        'Analysis Measures'[Total Units],
        ,
        DESC,
        DENSE
    ),
    BLANK ()
)

 

 

c) Other options like getting rid of that check altogether.

 

Finally, do note that you are not filtering out "Others" on the visual table, although you are using it as reference

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

 

 

Anonymous
Not applicable

Hi @AIB, 

Thank you, I have changed 'Segment'- 

If I could ask one more thing:

 

And Sorry maybe I am not understanding the RankX function. 

If I add a filter 'Geography'[GEO]= "China" straight away I get the wrong the ranking number.

How would I also add the GEO table so that this could also be filtered by. 

 

AndrewGriffiths_0-1611427176178.png

 

AlB
Super User
Super User

@Anonymous 

Rank Units V2 =
IF (
    HASONEVALUE ( 'Segment'[Segment] ),
    RANKX (
        FILTER ( ALL ( 'Company'[Company] ), 'Company'[Company] <> "Others" ),
        'Analysis Measures'[Total Units],
        ,
        DESC,
        DENSE
    ),
    BLANK ()
)

 If this doesn't work, I'd need to see the pbix (or a pbix with dummy data that reproduces your scenario)

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

 

Anonymous
Not applicable

Hi @AlB 

Here is a link to download a copy of the PBIX. 

https://1drv.ms/u/s!AtoYZpsBxPqIhsJwnIR3MtF3pcw0FA?e=lEh0Vv

I changed some of the data. 

Also you will see on page one the item I am trying to solve for. 

I want to replace the (BLANK) item in the blue box with the rank number. 

 

TAHNK YOU 

AlB
Super User
Super User

Hi @Anonymous 

Rank Units =
VAR aux_ =
    IF (
        HASONEVALUE ( 'Segment'[Segment] ),
        RANKX (
            FILTER ( ALLSELECTED ( 'Company'[Company] ), 'Company'[Company] <> "Others" ),
            'Analysis Measures'[Total Units],
            ,
            DESC,
            DENSE
        ),
        BLANK ()
    )
RETURN
    IF ( aux_ = 3, aux_ )

You can also exclude "Others" with a filter on the visual/page, rather than hard-coding it into the measure 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Anonymous
Not applicable

@AlB 

I do not want to return the company name, I want to return the rank, 

So:

IF Company = "HP inc"   what is the rank? 

 

@Anonymous 

??? The code above does return a rank, doesn't it?

Please show the expected result

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

Anonymous
Not applicable

Thank you for your help...

Expected result is that I add to a card. 

Then filter the visual Company="HP inc"  Or this can be hard coded into the measure. 

 

And result looks like below:

Card.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.