Forum Discussion

johnmelbourne's avatar
johnmelbourne
Helper V
3 years ago
Solved

RANKX query

Hi, Below is my scenario. Any assistance would be greatly appreciated. I have attached a pbix with the data. One note I didn't include below was, doesn't rank A as has no services (is blank).  ...
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    johnmelbourne,

     

    Try this solution.

     

    Data model:

     

     

    Measures:

     

    Total Complaints = SUM ( FactTable[Total Complaints] )
    Total Services = SUM ( FactTable[Total Services] )
    Rank = RANKX ( ALLSELECTED ( FactTable ), [Total Complaints],, ASC )

     

    Create a matrix using DimProvider[Provider] and the three measures. Add a visual filter:

     

     

    Result:

     

     

DataInsights's avatar
DataInsights
Super User
3 years ago

johnmelbourne,

 

Try this solution.

 

Data model:

 

 

Measures:

 

Total Complaints = SUM ( FactTable[Total Complaints] )
Total Services = SUM ( FactTable[Total Services] )
Rank = RANKX ( ALLSELECTED ( FactTable ), [Total Complaints],, ASC )

 

Create a matrix using DimProvider[Provider] and the three measures. Add a visual filter:

 

 

Result:

 

 

  • johnmelbourne's avatar
    johnmelbourne
    Helper V
    3 years ago

    Many thanks DataInsights , I really appreciate your time and response. The visual filter is necessary I believe. I couldn't work out any other way. Thanks. 

    John