Forum Discussion

prashantg364's avatar
prashantg364
Helper II
3 years ago

Column DAX query

I am having data as per the below table

ProductDateCategory
Alpha25-02-2023Service
Alpha17-02-2023Service
Alpha06-02-2023Purchase
Beta05-02-2023Purchase
Beta25-02-2023Service
Gama22-02-2023Service
Gama16-02-2023Service
Gama21-01-2023Purchase
Gama17-02-2023Service

I want to add one column in the last as shown below with the name "New Category"
Please suggest how to create this column.

ProductDateCategoryNew Category
Alpha25-02-2023ServiceService_2
Alpha17-02-2023ServiceService_1
Alpha06-02-2023PurchasePurchase
Beta05-02-2023PurchasePurchase
Beta25-02-2023ServiceService_1
Gama22-02-2023ServiceService_3
Gama16-02-2023ServiceService_1
Gama21-01-2023PurchasePurchase
Gama17-02-2023ServiceService_2

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    New Category CC =
SWITCH (
    TRUE (),
    Data[Category] = "Purchase", Data[Category],
    Data[Category] = "Service",
        Data[Category] & "_"
            & COUNTROWS (
                WINDOW (
                    1,
                    ABS,
                    0,
                    REL,
                    SUMMARIZE (
                        FILTER ( Data, Data[Category] = "Service" ),
                        Data[Product],
                        Data[Date]
                    ),
                    ORDERBY ( Data[Date], ASC ),
                    KEEP,
                    PARTITIONBY ( Data[Product] )
                )
            )
)
    • prashantg364's avatar
      prashantg364
      Helper II
      3 years ago

      Thanks

      but i am getting this output with your query

      ProductDateCategoryNew Category
      Alpha25-02-2023ServiceService_1
      Alpha17-02-2023ServiceService_1
      Alpha06-02-2023PurchasePurchase
      Beta05-02-2023PurchasePurchase
      Beta25-02-2023ServiceService_1
      Gama22-02-2023ServiceService_1
      Gama16-02-2023ServiceService_1
      Gama21-01-2023PurchasePurchase
      Gama17-02-2023ServiceService_1

       

      • prashantg364's avatar
        prashantg364
        Helper II
        3 years ago

        Output is not unique... 

        ProductDateCategoryNew Category
        Delta17-02-23PuchasePurchase
        Delta05-02-23ServiceService_1
        Delta06-02-23ServiceService_1
        Delta16-02-23ServiceSercvice_3
  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi prashantg364 

    you may also try to add a column like:

    Column = 
VAR _table = 
FILTER(
    TableName, 
    TableName[Product]=EARLIER(TableName[Product])
        &&TableName[Category]=EARLIER(TableName[Category])
)
VAR _index = RANKX(_table, [Date],,ASC)
RETURN
IF(
    COUNTROWS( _table)=1,
    [Category],
    [Category]&"_"&_index
)

     

    it worked like:

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi prashantg364 

    please try

    New category =
    VAR CurrentDate = 'Table'[Date]
    VAR T1 =
    CALCULATETABLE (
    'Table',
    ALLEXCEPT ( 'Table', 'Table'[Product], 'Table'[Category] )
    )
    VAR Check =
    COUNTROWS ( T1 ) > 1
    VAR T2 =
    ADDCOLUMNS (
    T,
    "NewCat",
    [Category] & "_"
    & IF ( Check, RANKX ( T1, [Date],, asc, DENSE ) )
    )
    RETURN
    MAXX ( FILTER ( T2, [Date] = CurrentDate ), [NewCat] )