Count number of computed column rows

[wokka]

Hi

I have a table that has its first column [Run_Date] in date format DD/MM/YY HH:MM:SS

The number of dates in this first column of the table is the result of already applied filters in powerbi.

I have worked out how to extract the year and month as computed columns  [year_extracted], [month_extracted]

year_extracted = YEAR([Run_Date])

month_extracted = MONTH([Run_Date])

My table looks like this :

[Run_Date]                      [year_extracted]  [month_extracted]

01/02/2024 12:00:00      2024.00               2.00

03/02/2024 12:00:00      2024.00               2.00

I want to count the number of rows in [month_extracted] column as for the month of February in the year 2024 and write it out as a value so I can put it in a bar graph.

The [year_extracted] has value value "2024.00" and  [month_extracted] has the value of   "2.00"   ( I coudnt get the column to be whole number format unfortunately )

This is bit of a problem for me, any help really appreciated, thank you.

[v-dineshya · Accepted solution]

Hi @wokka ,

Thank you for reaching out to the Microsoft Community Forum.

The .00 is happening because your computed columns are currently formatted as Decimal Numbers. You can either change their data types to Whole Number, or cast them to whole numbers in your DAX.

Here's how to fix both the formatting and get your desired row count:

Option 1: Fix Data Type and Use Simple COUNTROWS:

1.Fix column types in Power BI: Go to Data view. Select [year_extracted] and [month_extracted]. Change the data type from Decimal Number to Whole Number in the ribbon.

2.Then, create a measure like this:

RowCount_Feb_2024 =
CALCULATE(
COUNTROWS('YourTable'),
'YourTable'[year_extracted] = 2024,
'YourTable'[month_extracted] = 2
)

3.Add this measure to your bar chart.

Option 2: If You Cannot Change Column Types, Use INT to Cast:
If changing the column type isn't working, you can cast it to whole numbers in DAX:

RowCount_Feb_2024 =
CALCULATE(
COUNTROWS('YourTable'),
INT('YourTable'[year_extracted]) = 2024,
INT('YourTable'[month_extracted]) = 2
)

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @wokka ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @wokka ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @wokka ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[saritasw]

@wokka ,
In Power BI, just hover your mouse over the visual icon used in your chart, and a tooltip will appear showing the name of the visual.

[wokka]

HI, thanks.

Yes its defintely a table

My problem is I dont know what its called....unless I could create it as a virtual table using DAX code perhaps with a name and then I know what its called perhaps?

[saritasw]

@wokka ,
Good. Now you know what kind of visual you want to create. If you know how to write a DAX measure, 
Just use following code and replace it with your tablename from the data model. 

Feb2024_Count = 
CALCULATE(
    COUNTROWS(YourTableName),
    FILTER(
        YourTableName,
        INT(YourTableName[year_extracted]) = 2024 &&
        INT(YourTableName[month_extracted]) = 2
    )
)

See if you get what you need. And try to understand what and how its working. Thats how you will learn. Power BI is not that simple but be curious. You learn by doing. 

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you.
Sarita

[saritasw]

Hi @wokka ,

To count the number of rows for February 2024, you can create a measure instead of creating a calculated column using DAX as follows,

Feb2024_Count =
CALCULATE(
   COUNTROWS(YourTableName),
   FILTER(
      YourTableName,
      INT(YourTableName[year_extracted]) = 2024 &&
      INT(YourTableName[month_extracted]) = 2
   )
)

INT(...) is used to convert decimal values like 2024.00 as integers.
 

[wokka]

Thank you for your help

Sorry, I missed out some information - I have existing page level filters already in use on the page where this table was created. So anything I create on my powerbi page will be pre-filtered.

The table I have was made by clicking "table" in Visuializations, then dragging and dropping a column of an existing dimension table into it ( the date column ). 

So I do not know what this table Ive created on the desktop powerbi is actuallty called. 

Is there any way to find out please? Or does it reference itself with a name of "table" or something like that ?

Sorry, I am not that experienced in powerbi....

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please try split date column and time column, and please try creating data model with calendar dimension table like below. And then, the calculations might become easier to understand.

[wokka]

Thank you. 

Sorry, I missed out some information - I have existing page level filters already in use on the page. So anything I create on my powerbi page will be pre-filtered.

The table I have was made by clicking "table" in Visuializations, then dragging and dropping a column of an existing dimension table into it ( the date column ). 

So I do not know what this table Ive created on the desktop powerbi is actuallty called. Is there any way to find out please? Or does it reference itself with a name of "table" or something like that ?

Sorry, I am not that experienced in powerbi

[Jihwan_Kim]

Hi,

Please provide the link of the sample pbix file, like a link of dropbox / onedrive / or something else.

And then, I can try to look into it.

Thank you.

[wokka]

Hi  Jihwan_Kim,

Unfortunately I cant supply a pbix file ( due to strict security requirements ) but it can be reconstructed by anyone doing this :

(1) Create a few filters at page level on a powerbi page ( can be anything ) 

(2) Go to Visualisations, Click Table symbol to create new table anywhere on the page

(3) Drag & drop in a datetime column from any existing table into the new table on the desktop and call it Run_Date

(4) Create simple computed column that has year in one column, month in another column

year_extracted = YEAR([Run_Date])

month_extracted = MONTH([Run_Date])

(5) Count the number of rows in the month_extracted column that have a "2" in them ( for the month February ) & output that number to perhaps a variable, so it can then be graphed.

Thank you in advance.

[GrowthNatives]

Hi @wokka ,

You're very close! The issue you're facing is mainly due to data type formatting, not the logic itself. The YEAR() and MONTH() functions return whole numbers — if you're seeing 2024.00 and 2.00, it's just a formatting display issue, not actual decimals.

1. Ensure Column Data Type is Whole Number
If you've created the columns like this:

year_extracted = YEAR([Run_Date])
month_extracted = MONTH([Run_Date])

Go to Power BI Model view, select [year_extracted] and [month_extracted], and:

• Set Data Type to Whole Number
• Set Format to General Number (not Decimal Number)

To Count Rows for Feb 2024

Create a Measure:
This measure will count rows for February 2024:

Count_Feb_2024 = 
CALCULATE (
COUNTROWS ( YourTable ),
YourTable[year_extracted] = 2024,
YourTable[month_extracted] = 2
)

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀

[wokka]

Thank you, thats very hlepful, the problem I have is as I'm not that experienced in powerbi

The table I have was made by clicking "table" in Visuializations, then dragging and dropping a column of an existing dimension table into it. 

So I do not know what this table Ive created on the desktop powerbi is actuallty called. 

Is there any way to find out please? Or does it reference itself with a name of "table" ?