RANKX shows all 1 even if I use ALL at table and CALCULATE

[Jessie_Ip]

Hi there,

I have issue for RANKX.

All fields are from same table with below column:

DOD - a date with year, quarter, month, day

I want to rank the count of DOD by monthly basis and sort from Jan to Dec. The Month column is from DOD.

Here's the DAX: 

DoD Rank =

RANKX(ALL(BEREAVEMENTRAW), CALCULATE(COUNT(BEREAVEMENTRAW[DOD].[Date])),,desc,Dense)

I've already used ALL at table and CALCULATE to do the context transition. any advise why it still show 1 for all?

Many thanks,

Jessie

[vivek31]

DoD Rank =

RANKX(ALL(BEREAVEMENTRAW[month]), CALCULATE(COUNT(BEREAVEMENTRAW[DOD].[Date])),,desc,Dense)

[Jessie_Ip]

Hi @bhanu_gautam ,

I have tried as below:

DoD Rank =

RANKX(

    ALL(BEREAVEMENTRAW[DOD]),

    CALCULATE(

        COUNTROWS(BEREAVEMENTRAW),

        BEREAVEMENTRAW[DOD].[Month]), ,desc,Dense)

but it shows:

 i check DOD and it's datetime format as '25/02/2020 00:00:00' not value January. Any advise?

Thanks,

Jessie

[v-jianpeng-msft]

Hi, @Jessie_Ip 

I'm happy to answer your questions. We can achieve ranking by calculating columns and measures:

1.Implemented through measure：

We need to create two measures, measure2 is used to aggregate Count of DOD, measure is used to rank：

Measure2 DAX formula:

Measure 2 = SUM(Sheet3[Count of DOD])

Measure DAX formula:

Measure = CALCULATE(RANKX(ALL('Sheet3'),[Measure 2],,DESC,Dense))

The results are as follows:

2.Ranking is achieved by calculate columns:

Calculate columns DAX formula：

Column = RANKX(ALL(Sheet3),Sheet3[Count of DOD],,DESC,Dense)

The results are as follows:

I have provided the sample PBIX file used this time below. If the above suggestions are helpful to you, that would be great.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jessie_Ip]

Hi @v-jianpeng-msft 

Thank you for reply. DOD is a date so i use count as below:

COUNT_DOD = CALCULATE(COUNT(BEREAVEMENTRAW[DOD]), MONTH(BEREAVEMENTRAW[DOD]))

Then follow your way:

RANKX_DOD = CALCULATE(RANKX(ALL(BEREAVEMENTRAW), [COUNT_DOD],, DESC, Dense))

And it still show 

DOD is like this format: 25/02/2020 00:00:00

Any advise?

From what i find, I can't share the pbix file. If you can advise other way to share the file, I can try!

Many thanks,

Jessie

[v-jianpeng-msft · Accepted solution]

Hi, @Jessie_Ip 

Thank you for your reply. I understand your concerns, so I came up with a new way to calculate rankings. I created a new summary table through SUMMARIZE, and calculated the correct ranking through this summary table. According to your description DOD is date type data, I created the following data:

Create a new calculation table using the following DAX expression:

Table=SUMMARIZE (
    'Sheet5',
    Sheet5[Date].[Month],
    "Count DOD", CALCULATE ( COUNT ( 'Sheet5'[DOD] ), MONTH ( 'Sheet5'[DOD] ) )
)

The summary results are as follows:

Create a new column in this table for ranking. The DAX expression is as follows:

Rank = RANKX('Table','Table'[Count DOD],,DESC,Dense)

Ranking as follows:

Put this column into table visual:

I've provided the PBIX file used this time below. If you have any questions please let me know and I'll be happy to answer them.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nathan321]

The All(Berevementraw) needs a column reference, such as ALL(BEREVEMENT[DOD])

[bhanu_gautam]

@Jessie_Ip , Try using below method 

DoD Rank =
RANKX(
ALL(BEREAVEMENTRAW[DOD]),
CALCULATE(
COUNTROWS(BEREAVEMENTRAW),
BEREAVEMENTRAW[DOD].[Month]
),
,
DESC,
Dense
)

Please accept as solution and give kudos if it helps