topic Re: Column DAX query in DAX Commands and Tips

Column DAX query

prashantg364 — Thu, 02 Mar 2023 04:13:12 GMT

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

Re: Column DAX query

Jihwan_Kim — Thu, 02 Mar 2023 04:27:49 GMT

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

Re: Column DAX query

prashantg364 — Thu, 02 Mar 2023 05:05:59 GMT

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

Re: Column DAX query

prashantg364 — Thu, 02 Mar 2023 05:13:43 GMT

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

Re: Column DAX query

FreemanZ — Thu, 02 Mar 2023 05:47:12 GMT

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:

Re: Column DAX query

tamerj1 — Thu, 02 Mar 2023 06:11:37 GMT

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