Multiple condition

[Anonymous]

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/GF = SWITCH(TRUE(),IF(SEARCH("CHC", Example[Element], 1, 0) > 0 && SEARCH("GMC", [Dimension], 1, 0) > 0

,TRUE() ,FALSE() ),"CHC",IF(SEARCH("SPC",Example[Element], 1, 0) > 0 && SEARCH("GMC", [Dimension], 1, 0) > 0

,TRUE() ,FALSE() ),"SPC",IF(SEARCH("VAC", Example[Element], 1, 0) > 0 && SEARCH("GMC", [Dimension], 1, 0) > 0

,TRUE() ,FALSE() ),"VACCIN",IF(SEARCH("GENMED", Example[Element], 1, 0) > 0 && SEARCH("GMC", [Dimension], 1, 0) > 0

,TRUE() ,FALSE() ),"GENMED","GLOBAL" )

The problem is that when I format this, I realize that the total of the products is not good.
By investigating a little, I noticed that some products are in several categories.
I would like to add a condition that would say if you belong to more than one category other than Global then it is MBU.
In the example PBI, I have put everything, including a text box where I explain in detail.
I tried to make a copy of the table and play with the groupings and conditional columns but I still have more products.

Thank you very much for your future help.

Example Files 

[AlexisOlson]

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]

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 · Accepted solution]

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]

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]

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.

[Anonymous]

Hello @AlexisOlson, 

Thank you very much for your time and answer. 

The total amount is 4116. 

Is it working for you ? 

[AlexisOlson]

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

[David-Ganor]

Hi @Anonymous 

You might consider to change the true/false into 1 and 0...

Than instead of switch - just sum it...

Meaning: condition 1 + condition 2...etc...

If it is greater than 1..than MBU...

Other wise - your original formula...

[Anonymous]

Hello @David-Ganor 

I dont understand your suggestion. 

Target is to have a new categories because some users have more than one (ex: Genmed and Vaccin, .. ) 

If I change my condition like if element contain "CHC" then 1 and same for the other like 

Global = O, CHC=1, .. After I make a sum of rows how to determine the new categorie