Maximum allowed row limit is 1000000 while using measure in the card

Hi praveenk924730 ,

That error means your DirectQuery measure is generating a SQL query (or an intermediate step of that query) that produces more than 1,000,000 rows when no slicers are applied. Power BI hard-stops such queries in DirectQuery to protect the engine. Filters keep the rowset small, so your card works when a slicer is used. When you remove filters, your measure likely iterates down at the “hourly detail” grain (or cross-joins multiple hourly tables), tripping the 1M intermediate row limit.

Below is a pragmatic way to fix it, plus long-term options.

Something to try:

1. Force a date/hour filter before evaluating the ratio
   Make the card return BLANK (or a friendly message) unless the user picks a range. This avoids the unbounded scan that hits the limit.

   ValueInCard := VAR HasDateFilter = ISFILTERED('Date'[Date]) || ISFILTERED('Date'[DateTime]) || HASONEVALUE('Date'[Date]) RETURN IF ( NOT HasDateFilter, BLANK(), -- or use 0, or use a disconnected "Please select a date range" message DIVIDE ( [TotalTest end date hourly], [TotalTest start date hourly] ) )

2. Rewrite the two “TotalTest … hourly” measures to push aggregation
   Avoid row-by-row iterators over big tables. Aggregate at source grain in the filter context you need, then ratio.

   -- Base measures (no iterators) [Supply Hourly Total] = SUM ( 'Exchange Hourly Supply'[Qty] ) [Demand Hourly Total] = SUM ( 'Exchange Hourly Demand'[Qty] )
   
   -- Parameterized end/start measures that only change filter context:
   [TotalTest end date hourly] :=
   VAR EndDate = MAX ( 'Parameters'[EndDate] )
   RETURN
   CALCULATE ( [Supply Hourly Total], KEEPFILTERS ( TREATAS ( { EndDate }, 'Date'[Date] ) ) )
   
   [TotalTest start date hourly] :=
   VAR StartDate = MAX ( 'Parameters'[StartDate] )
   RETURN
   CALCULATE ( [Demand Hourly Total], KEEPFILTERS ( TREATAS ( { StartDate }, 'Date'[Date] ) ) )
   
   -- Then your card:
   ValueInCard =
   DIVIDE ( [TotalTest end date hourly], [TotalTest start date hourly] )

If your tables are truly separate (Supply, Demand, IX), keep each base measure on its own table and use CALCULATE + TREATAS to filter to the exact hour/date you want-don’t SUMX across detailed rows or build wide virtual tables unless you pre-aggregate first.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.