A new table with calculated column

[Anonymous]

Hello everyone!

I need help.

I got an excel with a lot of lines. I got 3 columns in it. A column Date, a column Name and a column Country.

I have plenty of dates in it (I can have the same date more than once).

I would like to create a new table with a range from the minimum of my dates to the maximum. (I already done that with this code:)

And I would like to creat a calculated column that will count the number of occurence for each date.

For exemple here is some date of my excel file:

I would like to have :

As there are 8 times sep-2017, I would like to have 8 in front of september...

The thing is that i don't succeed to creat a calculated column that will changes with the filter. The filter on the report can be on the name or on the country.

For exemple if we put a filter on France, i would like to have sept-17 and in the next column 4.

Indeed:

I put the pbix and the excel:

pbix: https://www.dropbox.com/s/riglii30hemlqz1/Test.pbix?dl=0

excel: https://www.dropbox.com/scl/fi/swxzq6kgea5caucvdyq3b/Test.xlsx?dl=0&rlkey=9oic1qyd42jyrp17h50o1aljl

Thank you

Have a nice day

[HotChilli · Accepted solution]

A calculated column (as you wanted above) won't respond to slicers so it won't help.

I don't know what your other measures do but it sounds like your current data model and measures are not working for you.   So you need a rethink -

1 - decide on the correct granularity of the main fact table

2 - get yourself a star schema as I advised above

3 - the reason I advised COUNTROWS is that it is very efficient

[Anonymous]

ok thank you! I will try

Have a nice day

[HotChilli]

Well done on posting the data.  Many could learn from you.
I don't think you should create the additional table.

I advise creating 3 dimension tables, Dates, Name, Country and link these with relationships to the Tableau table (let's change that name also).  Use these tables to make slicers with appropriate fields.

Create a simple measure using COUNTROWS on the Tableau table.

The Dates table can have any columns you need though it looks like you want a text column combining month and year as text so I'll give you that one:

MthAndYr = Dates[MthName] & " " & Dates[Yr]

MthName will be 

FORMAT(Dates[Date], "MMM") and i'll let you work out Yr.

Put MthAndYr and the countrows measure on a table visual

Good luck

[Anonymous]

@HotChilli  Thank you for your response but in my file I'm already using a measure that count the row using countx but I wanted a calculated column to optimize my program.

Indeed my original data got millions of lines and have plenty of measures that all use my measure with countx. So my report take many time to display and event say memory insuffisant when it tries to display a graph