Forum Discussion

eyeball's avatar
eyeball
Frequent Visitor
9 years ago
Solved

Sort by another column error

Hi,   I've created a cluster chart to display companies and the number of activities that our sales force have logged against them (Emails, calls etc).  I would like to sort the data by the pipelin...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi eyeball,

     

    My mistake! Please try the formula below.:smileylol:

    MaxPipeline =
VAR currentCompany = 'Table1'[company]
RETURN
    CALCULATE (
        MAX ( 'Table1'[pipeline] ),
        FILTER ( ALL ( Table1 ), 'Table1'[company] = currentCompany )
    )

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi eyeball,

 

As indicated in the error message, only the column which has unique value for each value in 'company' column can be used to sort the 'company' column.

 

In this scenario, I would suggest you to create a new column in the same table to calculate the MAX pipeline for each 'company'. In this way, the created column will has unique value for each value in 'company' column. Then you should be able to use the created column to sort the 'company' column. The formula below to create the calculate column is for your reference.:smileyhappy:

MaxPipeline =
VAR currentCompany = 'Table1'[company]
RETURN
    CALCULATE (
        MAX ( 'Table1'[pipeline] ),
        FILTER ( ALL ( Table1 ), 'Table1'[company] ) = currentCompany
    )

 

Regards

eyeball's avatar
eyeball
Frequent Visitor
9 years ago

Hi v-ljerr-msft

 

Thanks for the help.  I've used your example but it throws an error saying:

 

'A function 'FILTER' has been used in a True/False expression that is used as a table filter expression'

 

I have to admit that I haven't used DAX to create a function this way before so I'll have to read up on it!