Forum Discussion

DJKarma's avatar
DJKarma
Frequent Visitor
4 years ago
Solved

Create a conditional column based on contents of data

This is a simple request.
I have to create a column in query editor, or in table view. Whichever is easy.

Column looks like this -->

A,B,C,D,D,E,D

B,C,D,B,D,A

C,C,D,F,E,G

D,D,E,E,E,F,B

 

 

Result should be based on count of characters present, with 'A' character always taking the priority.
For instance result of the above column next to it will be

A ( A will take priority even if D has most count)

A (Even though B has most count,  A will take Priority)

C ( as C has most count)

E ( as E has most count)

 

Need Help
Show and Tell
  • tamerj1's avatar
    tamerj1
    4 years ago

    hI DJKarma 
    Please refer to attached file with the solution 

    Result = 
VAR Items = SUBSTITUTE ( 'Table'[Column], ",", "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 = GROUPBY ( T2, [@Item], "@Count", COUNTX ( CURRENTGROUP(), 1 ) )
VAR T4 = TOPN ( 1, T3, [@Count] )
VAR Result = MAXX ( T4, [@Item] )
RETURN
    IF ( PATHCONTAINS ( Items, "A" ), "A", Result )

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    hI DJKarma 
    Please refer to attached file with the solution 

    Result = 
VAR Items = SUBSTITUTE ( 'Table'[Column], ",", "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 = GROUPBY ( T2, [@Item], "@Count", COUNTX ( CURRENTGROUP(), 1 ) )
VAR T4 = TOPN ( 1, T3, [@Count] )
VAR Result = MAXX ( T4, [@Item] )
RETURN
    IF ( PATHCONTAINS ( Items, "A" ), "A", Result )
    • DJKarma's avatar
      DJKarma
      Frequent Visitor
      4 years ago

      Thanks, I am not able to check this, as there are also blanks in my column which I cant remove.
      Can you edit your code to handle blanks as well?
      If blank then output "XX"

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi DJKarma 
        See attached

        Result = 
VAR String = COALESCE ( 'Table'[Column], "XX" )
VAR Items = SUBSTITUTE ( String, ",", "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 = GROUPBY ( T2, [@Item], "@Count", COUNTX ( CURRENTGROUP(), 1 ) )
VAR T4 = TOPN ( 1, T3, [@Count] )
VAR Result = MAXX ( T4, [@Item] )
RETURN
    IF ( PATHCONTAINS ( Items, "A" ), "A", Result )
    • DJKarma's avatar
      DJKarma
      Frequent Visitor
      4 years ago

      A ( A will take priority even if D has most count)

      A (Even though B has most count,  A will take Priority)

      C ( as C has most count)

      E ( as E has most count)

       

       

      Just the characters from the column, i have written reasons in the bracket which you can exclude