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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JamesBurke
Helper II
Helper II

Rank x

Hi , 

 

I'm struggling with a rankx Dax calculation that would rank all deivces assoicated with a specific stores name , 

 

Imagine i have 30 stores with hundreds of devices But i only want to rank one store specfically from this list . 

 

AllSlected function is useful until you drill down creating that Filtered Device to always be rank 1. 

 

Item Rank Device name =
   CALCULATE(
     RANKX(
        ALL('Emporia Devices'[Device Name]), // NOT ALLSELECTED
        [Total Kwh]),
        'Emporia Devices'[Store Name] = "KP"
   )
 
Is what i have tried with the aim it returns a ranking of all the Devices that are in use by the KP store, however it ranks all devices from all stores and ranks them the same value

Any help would be Appericated. 
 
 
6 REPLIES 6
Greg_Deckler
Super User
Super User

@JamesBurke This may help: To *Bleep* with RANKX! - Microsoft Fabric Community

 

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Store NameDevice Usages
Store 1KP 113
Store 1 KP 218
Store 1KP 315
Store 2 SD 11
Store 2SD 2 32
Store 2SD 314

 

Expected outcome : 

 

Rank 1 : KP 2 

Rank 2 : KP 3 

Rank 3 : KP 1

 

Essentially i just want the ability to filter devices that start with KP from a list of Devices and Rank these based on usages. 

@JamesBurke So like this?

Rank = RANKX( FILTER( ALL('Table'), LEFT([Device],2) = "KP" ), CALCULATE(SUM([Usages])),, DESC )

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

 

Rank = 
RANKX(
FILTER( ALL('Emporia Devices'),
LEFT('Emporia Devices'[Device Name],2) = "KP" ),
CALCULATE(SUM('Emporia Device Usage'[Usages KWH])),, DESC )
 
This does not filter the device names to only rank KP Stores, it ranks all and not by Usages for some reason. 

@JamesBurke How about this?

Rank = 
IF( LEFT('Emporia Devices'[Device Name],2) = "KP",
RANKX(
FILTER( ALL('Emporia Devices'),
LEFT('Emporia Devices'[Device Name],2) = "KP" ),
CALCULATE(SUM('Emporia Device Usage'[Usages KWH])),, DESC ),
BLANK()
)

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

 

 CALCULATE(RANKX(
        ALL('Emporia Devices'[Device name KP]), // NOT ALLSELECTED
        [Total Kwh])
    )
 
I've made conditonal column and use the above Measure , is there a simple way to not include blanks in the rankings ?
 
thanks !

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.