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
harrinho
Helper III
Helper III

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

@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
        )
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

@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
        )
    )
)

Regards
Zubair

Please try my custom visuals

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 🙂 

 

@harrinho

 

So you need the Top BU name

 

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

 

 

 

 


Regards
Zubair

Please try my custom visuals

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

@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
        )
    )

Regards
Zubair

Please try my custom visuals

thank you very much @Zubair_Muhammad. It works perfect 🙂 

 

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

@harrinho

 

I created a dummy example to test it.

 

8500.png

 

 


Regards
Zubair

Please try my custom visuals

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.