Ranking of text column by count of instances - works for test table, but not for measure

austin15g — Wed, 17 Jul 2024 15:33:34 GMT

Hello all,

I am trying to create a measure that ranks by the number of instances a text field appears in a table. For reference, the table is a simple table for a single line for every conversation with a business executive and the process that was discussed. I am trying to count each instance of the process that was discussed and rank them from most important to least (highest to lowest). Below is a snip of the main table (FYI I had to create a calculated column to count the instances of each process because I couldn't figure out how to build that into the measure).

I am able to get the result that I want when I test it by creating a blank table, but when it comes to putting it in a measure and in a matrix visual, the rank value appears as 1s for every single row and I can't seem to figure out why. Here is the DAX code of the test table I created and a snip of the output.

test = ADDCOLUMNS ( SUMMARIZE ( 'FACT Mgmt Convos', 'FACT Mgmt Convos'[Secondary Processes Mentioned], 'FACT Mgmt Convos'[Count of Process] ), "Rank", RANK ( DENSE, SUMMARIZE ( 'FACT Mgmt Convos', 'FACT Mgmt Convos'[Secondary Processes Mentioned], 'FACT Mgmt Convos'[Count of Process] ), ORDERBY ( 'FACT Mgmt Convos'[Count of Process], DESC ) ) )

As you can see, the Rank column works just fine. Below is my DAX code for the measure which is pretty much the same exact thing.

Mgmt Mentions Ranking = RANK ( DENSE, SUMMARIZE ( 'FACT Mgmt Convos', 'FACT Mgmt Convos'[Secondary Processes Mentioned], 'FACT Mgmt Convos'[Count of Process] ), ORDERBY ( 'FACT Mgmt Convos'[Count of Process], DESC ) )

And here is a snip of the matrix visual.

Any guidance on why each row is populating with a rank of 1?

Re: Ranking of text column by count of instances - works for test table, but not for measure

Jihwan_Kim — Wed, 17 Jul 2024 17:01:34 GMT

Hi,

Please try something like below whether it suits your requirement.

Mgmt Mentions Ranking = RANK ( DENSE, SUMMARIZE ( ALLSELECTED ( 'FACT Mgmt Convos' ), 'FACT Mgmt Convos'[Secondary Processes Mentioned], 'FACT Mgmt Convos'[Count of Process] ), ORDERBY ( 'FACT Mgmt Convos'[Count of Process], DESC ) )

Re: Ranking of text column by count of instances - works for test table, but not for measure

austin15g — Wed, 17 Jul 2024 17:37:00 GMT

I was trying to toy around with ALLSELECTED but clearly wasn't putting it in the right position or something. Thank you so much, it worked perfectly!

topic Re: Ranking of text column by count of instances - works for test table, but not for measure in DAX Commands and Tips

Ranking of text column by count of instances - works for test table, but not for measure

Re: Ranking of text column by count of instances - works for test table, but not for measure

Re: Ranking of text column by count of instances - works for test table, but not for measure