Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Multiple condition

Hello Dear Community, I hope you are well. I have a small problem. I have created a column that gives me categories based on the content of a cell in another column. Here is the formula :  GBU/...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Is this better?

    GBU/GF = 
VAR ProdElements =
    CALCULATETABLE (
        VALUES ( Example[Element] ),
        ALLEXCEPT ( Example, Example[Product_ID] ),
        Example[Dimension] = "GMC"
    )
VAR Elements =
    FILTER (
        { "CHC", "SPC", "VAC", "GENMED", "GLOBAL" },
        NOT ISEMPTY (
            FILTER ( ProdElements, CONTAINSSTRING ( Example[Element], [Value] ) )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS ( Elements ) = 2 && "GLOBAL" IN Elements, EXCEPT ( Elements, { "GLOBAL" } ),
        COUNTROWS ( Elements ) > 1, "MBC",
        COUNTROWS ( Elements ) = 1, Elements,
        "GLOBAL"
    )

     

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

I don't see any elements in your example that have multiple categories but something like this should be closer to what you're after:

 

GBU/GF =
VAR Elements =
    FILTER (
        { "CHC", "SPC", "VAC", "GENMED", "GLOBAL" },
        CONTAINSSTRING ( Example[Element], [Value] )
    )
RETURN
    SWITCH (
        TRUE (),
        Example[Dimension] <> "GMC", "GLOBAL",
        COUNTROWS ( Elements ) > 1, "MBC",
        COUNTROWS ( Elements ) = 1, Elements,
        "GLOBAL"
    )

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

hello AlexisOlson , 

 

Thank you for your answer. 

Your formula doesnt work in my situation. 

I insert a text box to show you some users who have more than one Example 

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Is this better?

    GBU/GF = 
VAR ProdElements =
    CALCULATETABLE (
        VALUES ( Example[Element] ),
        ALLEXCEPT ( Example, Example[Product_ID] ),
        Example[Dimension] = "GMC"
    )
VAR Elements =
    FILTER (
        { "CHC", "SPC", "VAC", "GENMED", "GLOBAL" },
        NOT ISEMPTY (
            FILTER ( ProdElements, CONTAINSSTRING ( Example[Element], [Value] ) )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS ( Elements ) = 2 && "GLOBAL" IN Elements, EXCEPT ( Elements, { "GLOBAL" } ),
        COUNTROWS ( Elements ) > 1, "MBC",
        COUNTROWS ( Elements ) = 1, Elements,
        "GLOBAL"
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello AlexisOlson, 

       

      Thank you very much for your time and answer. 

      The total amount is 4116. 

      Is it working for you ? 

       

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        Not sure where you're getting that number from. The file you provided has 4,455 distinct Product_ID values.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      here is perhaps a simply view : 

      the target : 

       

      If the product has several elements (other than GLOBAL ex: CHC and SPC ) then Multi
      If the product has only one product it keeps its element

      We count Global only if product have ONLY GLOBAL (ex : CHC and GLOBAL then we keep CHC)

       

      Thank you 

       

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        Yes, this is what my logic is doing.

         

        According to your original post, it's also using "GLOBAL" when it doesn't match anything else.