Forum Discussion

gjohnson900's avatar
gjohnson900
Helper I
8 years ago

RANKX() over multiple columns?

I've got this as incoming data...

 

 

I'm sending the data to a Sankey chart, where the "weight" is the DISTINCTCOUNT of customerkeys.  I want to be able to offer the user a Top N chiclet slicer to keep the chart readable.  To that end, I created a computed measure [dRanker], that will RANKX the number of customers over my [source] values...

 

 

The problem is that the Sankey chart needs both a [source] and a [dest] to make it useful.  When I add a visual filter on the Sankey to only show where dRanker is <= 2, I get 9 different [source] values, not just the 2 highest ranked...

 

 

 

When I add [dest] to my data table, I can see that many more [source]s are getting RANKed as <= 2...  

 

 

I'm guessing that's why I'm getting so many results in the Sankey as well, so...  How do I tell RANKX() that I want it to rank over the DISTINCTCOUNT of CustomerKeys for BOTH [source] and [dest]?  I feel like the tSQL RANK() function allows this, but I can't seem to find similar overload functionality for RANKX() in DAX.

 

Another approach that would be just as good...  How could I get the Sankey chart to only show [source] values that appear in the original table above where the [source] ranking is correct?

 

Here's the dRanker DAX...

dRanker = RANKX(ALLSELECTED(FW17[source]), [CustCount])

... and the [CustCount] calculated measure...

CustCount = DISTINCTCOUNT(FW17[customerkey])

Am I thinking about this wrong?  What's wrong with my brain.  It's usually pretty good.

Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    Hi gjohnson900

    First you need to detemine how to calcuate the DISTINCTCOUNT of customerkeys, in your previous step, you just add the column [customerkeys] to the table  and select "count(distinct)", right? in this way, the result will change with the columns you add to the table.

    Thus your [Rank] column also become different.

    So first please let me know how you would like the DISTINCTCOUNT of customerkeys.

     

    Best regards

    maggie