SQL or DAX Count values in column based on their position

may I ask you for help?
How to count values based on their position?

column1
x
x
x
y
y
x
x
y

I need to have result like this:

x   3
y   2
x   2
y   1

Thank you!!!

I am using this SQL script but it does not work...

with test as (
	select *,row_number() over (order by (select null)) as countt
	FROM [vf_test_v02] as t
)
select column1,
	       ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY (select null)) as seqnum
from test 
group by column1,countt
ORDER BY countt

You should always pprovide a sorting order, else it costs precious time.

You can do in SQL following

CREATE TABLE vf_test_v02
    ("column1" varchar(1))
;
    
INSERT INTO vf_test_v02
    ("column1")
VALUES
    ('x'),
    ('x'),
    ('x'),
    ('y'),
    ('y'),
    ('x'),
    ('x'),
    ('y')
;

8 rows affected

with test as (
    select column1,row_number() over (order by (select null)) as rn
    FROM [vf_test_v02] as t
), LG as (SELECT
   rn,column1, CASE WHEN column1 <> LAG("column1") OVER(order by rn) THEN 1 ELSe 0 END lg
FROM test), SU as (
SELECT 
   column1, SUM(lg) OVER(order by rn) su
FROM LG)
SELECT
   column1, COUNT(*) countt
  FROM SU
  GROUP BY column1,su

fiddle