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 pipeline value of the company so that we see the biggest first.  When I select the company name (which is displayed on the chart) and then try to select pipeline in the the Sort by Column option in the Modeling tab I get an error the says:

 

We cannot sort the 'company' column by 'pipeline'.  You can't have more than one value in 'pipeline' for the same value in 'company'.  Choose a different column for sorting ir update the data in 'pipeline'.

 

The company and pipeline fields are in one table and the count of actvities are held in different tables related through companyId.

 

What would I need to do to get the chart sorted on pipeline?

 

Thanks!

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

8 Replies

  • 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!

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        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