cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
harrinho Member
Member

ALLEXCEPT Error - Wrong use?

Hi Everyone, 

 

I've gone thorugh similar posts but couldn't resolve my issue. I want to create a new column with the following statement: 

 

MAX Split BU 2 = IF((CDL_SVCS_Proj_Practice_Prod_Split[Top BU 100%])="0",MAXX(ALLEXCEPT(CDL_SVCS_Proj_Practice_Prod_Split,[Product Split %])=1,""))

 

However, it doesn't seem to work with a syntax like this. What I want to achieve here is this column to brings in the max value for every project ID where the Product Split % is NOT equal to 1.

 

What would be an alternative in order to achieve what this statement intends to do ?

 

Any help would be much appreciated. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: ALLEXCEPT Error - Wrong use?

@harrinho

 

Try this Column

 

Max Split BU 2 =
VAR myMax =
    CALCULATE (
        MAX ( TableName[Product Split %] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Project ID] ),
            TableName[Top BU 100%] = "SPLIT BU"
        )
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TableName[Top BU], 1 ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Project ID] ),
            TableName[Product Split %] = mymax
        )
    )

View solution in original post

7 REPLIES 7
Super User
Super User

Re: ALLEXCEPT Error - Wrong use?

@harrinho

 

Give this a shot

 

MAX Split BU 2 =
IF (
    TableName[TopBU100%] = 0,
    CALCULATE (
        MAX ( TableName[ColumnName] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[ProjectID] ),
            TableName[Product Split %] = 1
        )
    )
)
harrinho Member
Member

Re: ALLEXCEPT Error - Wrong use?

Thanks for following up Zubair_Muhammad

 

This looks better than my initial attempt but still doesn't achieve what I want. Let me try visualize it better, I've slightly changed some arguments but we can focus on the following,. So, as you can see in the attached table below 

 

 

we have multiple products for the same project ID. I want to populate the MAX Split BU 2 column with the TOP BU for the MAX Product Split %, when the TOP BU 100% = "SPLIT BU".

 

Therefore, in this example, MAX Split BU 2 column should be populated with AOsince the MAX Split % for this project for which the Top BU 100% is "SPLIT BU" is 0.4.

 

Perhaps I want all three MAX Split BU 2 lines to be populated with AO. I will then use this column values in a LOOKUP, so as when the project ID is found in another table (with an established relationship) to return the MAX Split BU 2 if the aTOP BU 100% = "SPLIT BU". This is the next step, for now I have to make the first bit working 🙂 

 

Super User
Super User

Re: ALLEXCEPT Error - Wrong use?

@harrinho

 

So you need the Top BU name

 

Please could you upload the file in onedrive or googledrive and share the link here

 

 

 

 

harrinho Member
Member

Re: ALLEXCEPT Error - Wrong use?

Thanks @Zubair_Muhammad

 

I think I'm close. I changed the query to:

 

MAX Split BU 2 = 
IF (
    CDL_SVCS_Proj_Practice_Prod_Split[Top BU 100%] = "SPLIT BU",
    CALCULATE (
        MAX ( CDL_SVCS_Proj_Practice_Prod_Split[Top BU] ),
        FILTER (
            ALLEXCEPT ( CDL_SVCS_Proj_Practice_Prod_Split, CDL_SVCS_Proj_Practice_Prod_Split[Project ID] ),
            CDL_SVCS_Proj_Practice_Prod_Split[Top BU 100%] = "SPLIT BU" 
        )
    )
)

But it returns the wrong value. Instead of AO which is the TOP BU for the MAX Split % (which is 0.4) it returns SM instead of AO

 

Screen Shot 2018-01-15 at 12.02.52.png

Super User
Super User

Re: ALLEXCEPT Error - Wrong use?

@harrinho

 

Try this Column

 

Max Split BU 2 =
VAR myMax =
    CALCULATE (
        MAX ( TableName[Product Split %] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Project ID] ),
            TableName[Top BU 100%] = "SPLIT BU"
        )
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TableName[Top BU], 1 ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Project ID] ),
            TableName[Product Split %] = mymax
        )
    )

View solution in original post

Super User
Super User

Re: ALLEXCEPT Error - Wrong use?

@harrinho

 

I created a dummy example to test it.

 

8500.png

 

 

harrinho Member
Member

Re: ALLEXCEPT Error - Wrong use?

thank you very much @Zubair_Muhammad. It works perfect 🙂 

 

This is a bit too technical for me but I think I get the logic. 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 271 members 2,882 guests
Please welcome our newest community members: