Forum Discussion
Row count problem in table
Hi Anonymous , 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.
Hi Anonymous
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
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.
Hi Anonymous , 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.