Forum Discussion

prashantg364's avatar
prashantg364
Helper II
3 years ago

Column DAX query

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