Forum Discussion

harrinho's avatar
harrinho
Helper III
8 years ago
Solved

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. 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

7 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      harrinho
      Helper III
      8 years ago

      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 AO, since 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 :) 

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        harrinho

         

        So you need the Top BU name

         

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