Forum Discussion

harrinho's avatar
harrinho
Icon for Helper III rankHelper 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_Spli...
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
        )
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity 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
    Icon for Helper III rankHelper 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
      Icon for Community Champion rankCommunity 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

       

       

       

       

      • harrinho's avatar
        harrinho
        Icon for Helper III rankHelper III
        8 years ago

        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