Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eyeball
Frequent Visitor

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!

1 ACCEPTED SOLUTION

Hi @eyeball,

 

My mistake! Please try the formula below.Smiley LOL

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

 

Regards

View solution in original post

8 REPLIES 8
v-ljerr-msft
Employee
Employee

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.Smiley Happy

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

 

Regards

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!

Hi @eyeball,

 

My mistake! Please try the formula below.Smiley LOL

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

 

Regards

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'

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.Smiley Happy

 

Regards

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?

Hi @eyeball,

 

Yes, the solution requires the company names to be unique to work correctly. If not, using companyId which is truly unique is needed.Smiley Happy

 

Regards

Hi @v-ljerr-msft

 

In that case, your solution works perfectly!  Thanks very much for all the help Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.