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!

  • 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

    • eyeball's avatar
      eyeball
      Frequent Visitor
      9 years ago

      Hi v-ljerr-msft

       

      OK, progress!!  This formula now works which is great but I think I may have not described the issue as clearly as I could have.  The table I'm using holds many sales opportunities that have a pipeline value but could be for the same company.  So, the raw data would look a little like this (in reality the Id's are Guids):

       

      OpptyId   CompanyId   PiplineValue

      1               1                   10

      2               1                   20

      3               2                   10

      4               2                   10

      5               1                   50

       

      The chart that I've created shows other agregated data but not the pipeline value.  I basically want to display the chart but sort it on the total pipeline value for each company so CompanyId 1 would have a pipeline value of 80 and CompanyId 2 would be 20 so CompanyId 1 would be first in the chart.

       

      I tweaked your function to look like this:

       

      MaxPipeline =

      VAR currentCompany = Temp_SR_Opportunity[companyid]

      RETURN

      CALCULATE ( SUM ( Temp_SR_Opportunity[sgam_pipelinevalue_base] ), FILTER ( ALL ( Temp_SR_Opportunity ), Temp_SR_Opportunity[companyid] = currentCompany ) )

       

      which sums the value perfectly.  However, when I then try to use the Sort By Column button I get the following error:

       

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

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

        Hi eyeball,

         

        Based on my test, your modified formula should work in your scenario. 

         

        Could you share a sample pbix file which can reproduce the issue? So that I could help further investigate on the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.:smileyhappy:

         

        Regards