Rankx return empty value

[vbourbeau]

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?

[mussaenda]

[vbourbeau]

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.

[vbourbeau]

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

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

[mussaenda]

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

[vbourbeau]

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

[vbourbeau]

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]

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

[v-danhe-msft]

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

[vbourbeau]

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?

[vbourbeau]

Ok I do a example see the attachment.

the result give that

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.

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

[v-danhe-msft]

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:

Regards,

Daniel He

[vbourbeau]

Stil there,

Can you send me your modify pbix

[v-danhe-msft]

Hi @vbourbeau ,

You could view the PBIX in my attachment.

Regards,

Daniel He

[vbourbeau]

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.

[v-danhe-msft · 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