Row count problem in table

[wokka]

Hi

I have a powerbi page that has some page level filters applied to it, so all data on the page is already filtered.

What I have done is clicked on "Table" under visualizations to created a new table on the page. Then I have dragged and dropped  an existing date and time column from an existing dimension table called [Run_Date] which has format of DD/MM/YY HH:MM:SS , and put it in the new table.

Then I worked out how to extract the year and month as computed columns  [year_extracted], [month_extracted] from the [Run_Date] column.

year_extracted = YEAR([Run_Date])

month_extracted = MONTH([Run_Date])

My table now 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

What I really need to do is count the number of rows that have both the [month_extracted] column of value of 2.00 and also [year_extracted] column value of 2024.00, then write that count value out to a variable so it can be put in a graph.

( in SQL you would write

Select count(*) from table where month_extracted = '2.00' and year _extracted = '2024.00'

)

I'm struggling with how to do something thats simple in SQL , in powerbi.

I cant use Powerquery due to security issues.

Any help is appreciated.

[v-hashadapu]

Hi @wokka , Thanks for the update. We are happy to hear that you have resolved the issue. Thanks for sharing the details here. Please mark your insights 'Accept as solution' to help others with similar problems find it easily. 
Thank you.

[mdaatifraza5556]

Hi @wokka 

Can you please try the steps below?

Create a measure using DAX below.

Count_Filtered =
CALCULATE(
COUNTROWS(YourTableName),
YourTableName[month_extracted] = 2,
YourTableName[year_extracted] = 2024
)

If this answers your questions, kindly accept it as a solution and give kudos.

[bhanu_gautam]

@wokka Go to the "Modeling" tab.
Click on "New Measure".

CountFilteredRows =
CALCULATE(
COUNTROWS('YourTableName'),
'YourTableName'[month_extracted] = 2,
'YourTableName'[year_extracted] = 2024
)

You can now add this measure to any visual, such as a card or a graph, to display the count of rows that meet your criteria.

[dk_dk]

Hi @wokka 

You need to create a measure and use the CALCULATE function.

Something like the below:

Measure = CALCULATE(COUNTROWS([Table]),Table[month_extracted]=2.00,Table[year_extracted]=2024.00

If you otherwise do not need the extracted columns you can do it all in a single measure referencing the Run_Date:
Measure = CALCULATE(COUNTROWS([Table]),YEAR(Table[Run_Date])=2024,MONTH(Table[Run_Date])=2)

You can find more information about CALCULATE here and here.

Let me know if you run into any issues.

Best regards.
Daniel

[wokka]

Hi

Thank you everyone for your help so far.

I do have one major problem - as I have manually created a table on the powerbi desktop by clicking  on the table Visualiztion, I literally have no idea what this created table name is unfortunately. 

This what I use to create the table :

While I do drag in one column from another table into this table, maybe you would call what Ive created a on the desktop a virtual table?

The parts bolded below is the part I literally have no idea what its called, which means I assume I cant use it in a calculation?

CALCULATE(COUNTROWS([Table]),Table[month_extracted]

I cant use power query to do anything ( we arent allowed ) so its all click and drag on the desktop.

Hope this helps.

[v-hashadapu · Accepted solution]

Hi @wokka , Thank you for reaching out to the Microsoft Community Forum.

When you create a table visual in Power BI by dragging fields like [Run_Date] into the canvas, you're not creating a new data table, you're just displaying rows from an existing table in your data model. To write DAX that references that data, you need to use the name of the original table from which [Run_Date] comes. You can find this name in the Fields pane on the right side of Power BI Desktop. If you're still not sure, switch to the Model view and find the table that includes the [Run_Date] column.

If you already have calculated columns like [year_extracted] and [month_extracted], try below example measure:

CountFilteredRows =

CALCULATE(

    COUNTROWS('RunData'),

    'RunData'[year_extracted] = 2024,

    'RunData'[month_extracted] = 2

)

If extracted columns aren’t needed, use DAX Directly with the Date Column. Example:

CountFilteredRows =

CALCULATE(

    COUNTROWS('RunData'),

    YEAR('RunData'[Run_Date]) = 2024,

    MONTH('RunData'[Run_Date]) = 2

)

Once you create the measure, it will appear in the Fields pane under the 'RunData' table. You can drag it into a Card, Bar chart or any other visual to show or compare the row count.

Please refer below documentation:

Transform, shape, and model data in Power BI

Create measures for data analysis in Power BI Desktop

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.