Column DAX query

[prashantg364]

I am having data as per the below table

Product	Date	Category
Alpha	25-02-2023	Service
Alpha	17-02-2023	Service
Alpha	06-02-2023	Purchase
Beta	05-02-2023	Purchase
Beta	25-02-2023	Service
Gama	22-02-2023	Service
Gama	16-02-2023	Service
Gama	21-01-2023	Purchase
Gama	17-02-2023	Service

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

Product	Date	Category	New Category
Alpha	25-02-2023	Service	Service_2
Alpha	17-02-2023	Service	Service_1
Alpha	06-02-2023	Purchase	Purchase
Beta	05-02-2023	Purchase	Purchase
Beta	25-02-2023	Service	Service_1
Gama	22-02-2023	Service	Service_3
Gama	16-02-2023	Service	Service_1
Gama	21-01-2023	Purchase	Purchase
Gama	17-02-2023	Service	Service_2

[tamerj1]

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

[FreemanZ]

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:

[Jihwan_Kim]

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]

Thanks

but i am getting this output with your query

Product	Date	Category	New Category
Alpha	25-02-2023	Service	Service_1
Alpha	17-02-2023	Service	Service_1
Alpha	06-02-2023	Purchase	Purchase
Beta	05-02-2023	Purchase	Purchase
Beta	25-02-2023	Service	Service_1
Gama	22-02-2023	Service	Service_1
Gama	16-02-2023	Service	Service_1
Gama	21-01-2023	Purchase	Purchase
Gama	17-02-2023	Service	Service_1

[prashantg364]

Output is not unique... 

Product	Date	Category	New Category
Delta	17-02-23	Puchase	Purchase
Delta	05-02-23	Service	Service_1
Delta	06-02-23	Service	Service_1
Delta	16-02-23	Service	Sercvice_3