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

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

     

9 Replies

  • David-Ganor's avatar
    David-Ganor
    Resolver II
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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

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