Row count issue

[dmb6996]

Hi,

When I count the rows of my query I have 30K rows, if I try to load all rows from a calculated column within that query it counts millions of rows.

What is causing this? How can I make sure that its counting 30K rows in the calculated column as well? The calculated column is checking whether a key in the current query appears in 1 other query.

[Anonymous · Accepted solution]

Hi @dmb6996 ,

When you count the number of rows in a query in Power BI, you are actually looking at the number of rows in the query's result set, which is the number of rows in the table after you filtered, aggregated, or otherwise manipulated it. This value is a count of the number of rows in the table after your filtering and manipulation.
However, when you try to load all the rows from the calculated column in that query, there is a calculation mechanism involved for the calculated column. Calculated columns are calculated row-by-row as the data model is loaded or as the query is run, rather than simply counting the number of rows in the result set. This means that the way calculated columns are calculated can result in more rows being created or counted, leading to the millions of rows you observe.
This typically occurs when the calculated column contains references to other tables or queries, especially when one-to-many relationships are involved.

In my test, there was a model consisting of a Sales table and an Employees table. The original row record data was only three, but the calculated column created resulted in five, due to the one-to-many relationship between the tables.

IsEmployeeValid = 
IF (
    COUNTROWS (
        FILTER (
            RELATEDTABLE('Employees'),
            'Employees'[EmployeeID] = 'Sales'[EmployeeID]
        )
    ) > 0,
    "Valid",
    "Invalid"
)

If the problem persists, please provide the DAX expression used in the calculated column and the relationship between the data models involved.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly