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
vbourbeau
Resolver II
Resolver II

Rankx return empty value

Hi,

 

I have a matrix where I want to return the rank of my customer by total sales for a specific period.

I put this DAX

rank = rankx(ALLSELECTED(InvcDtl[Customer Name]);calculate(sum(InvcDtl[Total invoice amount])))
 
All work... my customer have their rank base on their sales. But when I change the time period  if some customers do not have sales for this period well the customer still there empty?
 
I don't want to see my old client?
 
Is some one can help?
 
2019-04-02 15_07_38-Ventes v1.5.1 - Power BI Desktop.png
 
1 ACCEPTED SOLUTION

Hi @vbourbeau ,

Maybe you could refer to below link about rank with hierarchy:

https://dataninjago.com/2017/12/11/dax-ranking-with-hierarchy-drill-down/

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

15 REPLIES 15
mussaenda
Super User
Super User

Untitled.png

Hi,

 

With my true data when I apply the filter the matrix give me this error:

there's not enough memory to complete this operation

I restart my computer and nothing.

Without the filter the matrix run in a sec.

Look If I add a line in my matrix the rank lost is mind? See the pbix

 

2019-04-03 12_02_00-Window.png

 

link to te file https://we.tl/t-0gcXo9Adwq

Untitled.png

 

rank = RANKX ( ALLSELECTED ( Feuil1[Part] ) , CALCULATE ( SUM ( Line[amount] ) ) )

I want to rank the client, as we see in my example.pbix

 

2019-04-04 08_05_01-Window.png

 

Thank you

 

Do you know if it's my dax the problem, because if the date are in the same table as the invoice the problem don't occure? kind of relationship with both table?

 

mussaenda
Super User
Super User

Maybe you can filter the blank in Filters Pane where mTotal Sales is not blank.

v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @vbourbeau ,

Could you please post some simple sample data and your desired result to have a test if possible? 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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vbourbeau
Resolver II
Resolver II

I realize that my date measure are on another table that InvcDtl. It's the reason why the rank took all my customer. But my date table are linked with my InvcDtl table? Why the Date filter won't apply?

Ok I do a example see the attachment.

 

the result give that

2019-04-03 08_26_39-Sans titre - Power BI Desktop.png

ClienD and ClientE don't have any sales in 2016 but the table show them?

 

If I remove my Rank measure the filter work ok.

2019-04-03 08_28_36-Sans titre - Power BI Desktop.png

 

I don't know how to add my pbix so I create a link. https://we.tl/t-f0OQ4nhFOm

 

 

Hi @vbourbeau ,

Based on my test, you could refer to below formula:

Rank = IF(CALCULATE(SUM(Line[amount]))=BLANK(),BLANK(),rankx(ALLSELECTED(Line[Client]),CALCULATE(SUM(Line[amount]))))

Result:

1.PNG

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Stil there,

Can you send me your modify pbix

 

2019-04-10 10_58_32-Window.png

 

Hi @vbourbeau ,

You could view the PBIX in my attachment.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

OK but it's a table I need a Matrix to be able to drill down to another level. 

The problem is there when we add more level.

Hi @vbourbeau ,

Maybe you could refer to below link about rank with hierarchy:

https://dataninjago.com/2017/12/11/dax-ranking-with-hierarchy-drill-down/

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors