topic Re: RANKX shows all 1 even if I use ALL at table and CALCULATE in DAX Commands and Tips

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

Anonymous — Tue, 06 Feb 2024 16:00:31 GMT

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

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

bhanu_gautam — Tue, 06 Feb 2024 16:22:19 GMT

Anonymous , 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

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

Nathan321 — Wed, 07 Feb 2024 01:05:10 GMT

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

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

Anonymous — Wed, 07 Feb 2024 07:35:25 GMT

Hi, Anonymous

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.

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

Anonymous — Thu, 15 Feb 2024 16:04:58 GMT

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

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

Anonymous — Thu, 15 Feb 2024 16:12:46 GMT

Hi Anonymous

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

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

Anonymous — Tue, 20 Feb 2024 08:30:34 GMT

Hi, Anonymous

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.

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

vivek31 — Tue, 20 Feb 2024 15:31:43 GMT

DoD Rank =

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