COUNTING ROWS DAX TABLE

[Syndicate_Admin]

Hello Good day,

I'm having a table where I have a list of car brands and I need to do two types of counting in Dax:

1. The first count is called CONTEO_A, and in this field I need to count how many times each brand of car is repeated within the total records.

2. The second count is called CONTEO_B, and in this field I need to count consecutively the number of times the car mark is repeated.

Here is an image of what I need to do:

MARCH	CONTEO_A	CONTEO_B
Acura	2	1
Acura	2	2
Audi	2	1
Audi	2	2
BMW	1	1
Buick	4	1
Buick	4	2
Buick	4	3
Buick	4	4
Cadillac	2	1
Cadillac	2	2
Chevrolet	4	1
Chevrolet	4	2
Chevrolet	4	3
Chevrolet	4	4

For example the Chevrolet brand is repeated 4 times within the list, for this reason the CONTEO_A field, has the number 4, but the CONTEO_B field, has the consecutive counting numbers from 1 to 4.

Thank you very much for your help.

[Syndicate_Admin]

Hello, not actually the table has no date, but I see that it is necessary to be able to perform the counts, how can I implement that date column that is not manually?

[Jihwan_Kim]

Hi,

If you do not have date column, what other columns does this table have? Index number column? Or, please share your sample pbix file's link here, and then I can try to look into it to come up with a more accurate solution.

Or, you can try to add index column in just one click in Power Query Editor.

Thanks.

[Syndicate_Admin]

Hello, if the table has index, it is a large list of brands, I send you the .pbix, I appreciate your help. I send you the data here I did not know how to attach the .pbix

Index	MARCH
1	Acura
2	Acura
3	Audi
4	Audi
5	BMW
6	Buick
7	Buick
8	Buick
9	Buick
10	Cadillac
11	Cadillac
12	Chevrolet
13	Chevrolet
14	Chevrolet
15	Chevrolet

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for sharing.

If your table has an index column like above, please check the attached pbix file below.

I changed the sample in the pbix file.

I hope this helps.

[Jihwan_Kim]

Hi,

I assume the table has something like a date column, like below.

Please check the below picture and the attached pbix file.

It is for creating a new columns.

Conteo A CC = 
VAR _currentmonth =
    MONTH ( Data[Date] )
VAR _currentbrand = Data[Brand]
RETURN
    COUNTROWS (
        FILTER (
            Data,
            MONTH ( Data[Date] ) = _currentmonth
                && Data[Brand] = _currentbrand
        )
    )

Conteo B CC =
VAR _currentmonth =
    MONTH ( Data[Date] )
VAR _currentdate = Data[Date]
VAR _currentbrand = Data[Brand]
RETURN
    COUNTROWS (
        FILTER (
            Data,
            MONTH ( Data[Date] ) = _currentmonth
                && Data[Date] <= _currentdate
                && Data[Brand] = _currentbrand
        )
    )