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 ...
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
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] )