UPPER first occurance in a column

[mrthib]

I am creating a table that tracks different software versions that have been pushed to different user groups. Since these do not change everyday I used the fill function in Power Query to fill down from the latest version. The table is fairly large so in order to quickly find when a new version was sent out I would like to capitalize the first occurance of every version. I am trying to do this in DAX but having some weird issues.

Here is a sample of the data

Date	Column1
1/1/2018	a
1/2/2018	a
1/3/2018	a
1/4/2018	b
1/5/2018	c
1/6/2018	c
1/7/2018	c
1/8/2018	c
1/9/2018	c
1/10/2018	d
1/11/2018	d
1/12/2018	d
1/13/2018	e
1/14/2018	f
1/15/2018	f
1/16/2018	f
1/17/2018	f
1/18/2018	f
1/19/2018	g
1/20/2018	g
1/21/2018	g

The calculated column I wrote was

Column2 2 = IF(Sheet1[Column2] = CALCULATE(VALUES(Sheet1[Column2]), 
            DATEADD(Sheet1[Date], -1, DAY))
            , Sheet1[Column2], UPPER(Sheet1[Column2]))

The problem though is that it just returns a table of all caps!

Date	Column2	Column2 2
1/1/2018	a	A
1/2/2018	a	A
1/3/2018	a	A
1/4/2018	b	B
1/5/2018	c	C
1/6/2018	c	C
1/7/2018	c	C
1/8/2018	c	C
1/9/2018	c	C
1/10/2018	d	D
1/11/2018	d	D
1/12/2018	d	D
1/13/2018	e	E
1/14/2018	f	F
1/15/2018	f	F

If I replace the 'if true' part of the function with '1' and if false with '0' it returns 1's and 0's in the correct spots. I am not sure if there is some row context I am missing or something.

This is what it should return

Date	Column2	Column2 2
1/1/2018	a	A
1/2/2018	a	a
1/3/2018	a	a
1/4/2018	b	B
1/5/2018	c	C
1/6/2018	c	c
1/7/2018	c	c
1/8/2018	c	c
1/9/2018	c	c
1/10/2018	d	D
1/11/2018	d	d
1/12/2018	d	d
1/13/2018	e	E
1/14/2018	f	F
1/15/2018	f	f

[ChandeepChhabra]

@mrthib : Ashish is correct, I am having the same error as his

A quick workaround is to concatenate the UPPER with space " "

UPPER(Data[Column1]&" ") It somehow works this way

[mrthib]

Thanks @ChandeepChhabra, this work around works pretty well. The only issue with it is that it capitalizes the most recent version no matter if it is the first occurance or not. It is only doing this on my data set though and I could not replicate the issue with the mock data set I put in the question. This whole thing is some very strange behavior.

[v-yulgu-msft]

Hi @mrthib,

This issue has been reported internally, CRI: 67060820.

Regards,

Yuliana Gu

[Ashish_Mathur]

Hi,

I can confirm that there is a clear bug there.  I tried this calculated column formula and the result is the same as yours

=if(ISBLANK(CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Version]=EARLIER(Table1[Version])&&Table1[Date]<EARLIER(Table1[Date])))),UPPER(Table1[Version]),LOWER(Table1[Version]))