Forum Discussion

MaleneL's avatar
MaleneL
Icon for Resolver I rankResolver I
1 year ago
Solved

Calculated most used text in new column

I have this table (it is simplified) that I want to create a new column in (the one could outcome)

But my formular do not work

Customer ID

Product

Outcome

1

A

A

1

A

A

1

A

A

1

B

A

1

B

A

2

C

B

2

B

B

2

B

B

2

A

B

3

C

C

3

C

C

3

B

C

4

B

B

4

B

B

4

X

B

 

I would like to create a new column similar with Product but always with the same value for the Customer ID. That value should be the one that appears the most in Product.

I have built this measurement:

VAR _Count =

    CALCULATE(

        COUNT(Tabel[Product]),

        ALLEXCEPT(Tabel, Tabel [Customer ID], Tabel [Product])

    )

VAR _d_Table =

    SUMMARIZE(

        Tabel,
        Tabel [Customer ID],
        Tabel [Product]

        "Count", _Count

    )

RETURN

    CALCULATE(

        FIRSTNONBLANK(Tabel [Product], 1),

        FILTER(_d_Table, EARLIER(Tabel [Customer ID]) = Tabel [Customer ID])

    )

 

But I just get det same result as in the product column and not the desired outcome

Need Help
  • Shahid12523's avatar
    Shahid12523
    1 year ago

    DAX for Calculated Column

     

    Outcome =
    VAR CurrentCustomer = Table1[Customer ID]
    VAR ProductCounts =
    ADDCOLUMNS (
    SUMMARIZE ( Table1, Table1[Customer ID], Table1[Product] ),
    "ProdCount",
    CALCULATE (
    COUNTROWS ( Table1 ),
    ALLEXCEPT ( Table1, Table1[Customer ID], Table1[Product] )
    )
    )
    VAR TopProduct =
    TOPN (
    1,
    FILTER ( ProductCounts, [Customer ID] = CurrentCustomer ),
    [ProdCount],
    DESC
    )
    RETURN
    MAXX ( TopProduct, Table1[Product] )

  • johnt75's avatar
    johnt75
    1 year ago

    You can create a calculated column like

    Test = 
VAR ProductsAndCount = CALCULATETABLE(
    SUMMARIZECOLUMNS(
        'Table'[Product],
        "@num", COUNTROWS( 'Table' )
    ),
    ALLEXCEPT( 'Table', 'Table'[Customer ID] )
)
VAR Result = SELECTCOLUMNS(
    INDEX( 1, ProductsAndCount, ORDERBY( [@num], DESC ) ),
    "@prod", 'Table'[Product]
)
RETURN Result

3 Replies

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

    DAX for Calculated Column

     

    Outcome =
    VAR CurrentCustomer = Table1[Customer ID]
    VAR ProductCounts =
    ADDCOLUMNS (
    SUMMARIZE ( Table1, Table1[Customer ID], Table1[Product] ),
    "ProdCount",
    CALCULATE (
    COUNTROWS ( Table1 ),
    ALLEXCEPT ( Table1, Table1[Customer ID], Table1[Product] )
    )
    )
    VAR TopProduct =
    TOPN (
    1,
    FILTER ( ProductCounts, [Customer ID] = CurrentCustomer ),
    [ProdCount],
    DESC
    )
    RETURN
    MAXX ( TopProduct, Table1[Product] )

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    You can create a calculated column like

    Test = 
VAR ProductsAndCount = CALCULATETABLE(
    SUMMARIZECOLUMNS(
        'Table'[Product],
        "@num", COUNTROWS( 'Table' )
    ),
    ALLEXCEPT( 'Table', 'Table'[Customer ID] )
)
VAR Result = SELECTCOLUMNS(
    INDEX( 1, ProductsAndCount, ORDERBY( [@num], DESC ) ),
    "@prod", 'Table'[Product]
)
RETURN Result
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi MaleneL 

    Try this calculated column

    outcome2 = 
VAR _customerID = _table[Customer ID]
VAR _tbl =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( _table, _table[Customer ID] = _customerID ),
            _table[Customer ID],
            _table[Product]
        ),
        "@count", CALCULATE ( COUNTROWS ( _table ) )
    )
VAR _topN =
    TOPN ( 1, _tbl, [@count], DESC )
RETURN
    MAXX ( _topN, [Product] )

     

    or as a measure

    outcome measure = 
VAR _customerID = SELECTEDVALUE(_table[Customer ID])
VAR _tbl =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( ALL( _table ), _table[Customer ID] = _customerID ),
            _table[Customer ID],
            _table[Product]
        ),
        "@count", CALCULATE ( COUNTROWS ( _table ) )
    )
VAR _topN =
    TOPN ( 1, _tbl, [@count], DESC )
RETURN
    MAXX ( _topN, [Product] )