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

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

      • eyeball's avatar
        eyeball
        Frequent Visitor
        9 years ago

        Hi v-ljerr-msft

         

        I've been doing a bit of investigation and I think I may have found why I get the error.  I created a calculated table and populated it with a ditinct list of companies and the summed pipeline value for each.  I then created a chart of all the companies and displayed then pipline value.  I have instances where company names are identicle, even though technically they are considered different.  To use an example, I could have a branch of ABC Ltd in London and a branch of ABC Ltd in New York.  I believe that when I try to sort against the name column it throws the error because is see them as dupes and doesn't know which pipeline value to use.

         

        If I changed the chart to use companyId which is a truly unique Id and then sort pipeline against that, it all seems to work!

         

        Does that sound correct?