Power BI – Card “Opening” Count Works but Table Rows Do Not Show (Backlog Logic)

[Taher28]

Power BI – Card “Opening” Count Works but Table Rows Do Not Show (Backlog Logic)

Scenario / Requirement
I have a report with multiple status cards (Opening, New, Moved, Drop, Closing).
When a user select date range and clicks a card, the table below should show the exact rows that make up that card count.

This pattern works correctly for New, Moved, Drop, and Closing, but fails only for Opening.

Business Logic for “Opening”

“Opening” represents backlog items carried forward into the selected period:

• Created before the selected period start
• Still active during the selected period (based on Agreement Signed / Submitted / Award dates)
• The count must ignore the Created Date slicer

Working Card Measure (Opening Count)

Opening Count =

VAR UserStartDate =

    MIN ( 'Consolidated Grants'[Created Date] )

VAR SelectedYear =

    YEAR ( UserStartDate )

VAR ForcedEndDate =

    DATE ( SelectedYear, 12, 31 )

RETURN

CALCULATE (

    DISTINCTCOUNT ( 'Consolidated Grants'[Grant ID & Chatter] ),

    ALL ( 'Consolidated Grants'[Created Date] ),

    'Consolidated Grants'[Agreement Signed Date.] >= UserStartDate,

    'Consolidated Grants'[Agreement Signed Date.] <= ForcedEndDate,

    'Consolidated Grants'[Created Date] < UserStartDate

)

This measure shows the correct Opening count in the card.

Attempted Row-Level Filter for Table (Not Working)

Is Opening =

VAR RowCreatedDate =

    SELECTEDVALUE ( 'Consolidated Grants'[Created Date] )

VAR RowAgreementDate =

    SELECTEDVALUE ( 'Consolidated Grants'[Agreement Signed Date.] )

VAR UserStartDate =

    MIN ( 'Consolidated Grants'[Created Date] )

VAR SelectedYear =

    YEAR ( UserStartDate )

VAR ForcedEndDate =

    DATE ( SelectedYear, 12, 31 )

RETURN

IF (

    NOT ISBLANK ( RowCreatedDate )

        && RowCreatedDate < UserStartDate

        && RowAgreementDate >= UserStartDate

        && RowAgreementDate <= ForcedEndDate,

    1,

    0

)

This measure is applied as a Visual-level filter on the table (Is Opening = 1),
but the table shows no rows, even though the card count is non-zero.

What I Already Tried

1. Disabled Date slicer interaction with the table
2. Used ALL(Created Date) in the card measure
3. Used a disconnected Status Selector for card selection
4. Same filtering pattern works for other statuses
5. Table still does not return Opening rows

Key Question

How can a CALCULATE + ALL() backlog measure (Opening) be correctly translated into row-level table filtering?

Any guidance or recommended pattern would be appreciated.

[v-achippa]

Hi @Taher28,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @FBergamaschi, @FreemanZ, @Olufemi7 and @Jaywant-Thorat for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Taher28,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[Jaywant-Thorat · Accepted solution]

Excellent question, this is a classic DAX context mismatch problem, and your instincts are actually correct. The issue is not your business logic; it’s how row context vs filter context behaves when you try to reuse a backlog-style measure at row level.

Let’s break it cleanly and then I’ll give you the correct, reliable pattern.

Why the Card Works but the Table Shows No Rows?
The core reason (this is the key)

Your Opening Count measure works because:

• It is evaluated once in a pure filter context
• ALL ( Created Date ) removes the slicer
• MIN ( Created Date ) comes from the slicer context
• CALCULATE builds a virtual filtered set of rows

Perfect for aggregation (card)

But the table filter fails because:

SELECTEDVALUE() is the wrong tool here

In a table:

• Each row already has a row context
• But SELECTEDVALUE does NOT read row context
• It only works when exactly one value exists in filter context

Result:

- RowCreatedDate = BLANK

- RowAgreementDate = BLANK

- Your IF condition → always FALSE

- T able returns zero rows

👉 This is expected DAX behavior, not a bug.

Important Rule to Remember:

Row-level filtering must reproduce the same filter logic as the measure — not reinterpret it row by row using SELECTEDVALUE.

Solution:

>> Step1: Create a Boolean-style filter measure (NO SELECTEDVALUE)

---DAX---

Is Opening =
VAR UserStartDate =
CALCULATE (
MIN ( 'Consolidated Grants'[Created Date] ),
ALLSELECTED ( 'Consolidated Grants' )
)

VAR SelectedYear =
YEAR ( UserStartDate )

VAR ForcedEndDate =
DATE ( SelectedYear, 12, 31 )

RETURN
IF (
CALCULATE (
COUNTROWS ( 'Consolidated Grants' ),
ALL ( 'Consolidated Grants'[Created Date] ),
'Consolidated Grants'[Created Date] < UserStartDate,
'Consolidated Grants'[Agreement Signed Date.] >= UserStartDate,
'Consolidated Grants'[Agreement Signed Date.] <= ForcedEndDate
) > 0,
1,
0
)

---DAX---

Why this works

• The row is filtered naturally

• No need to read row values manually

• DAX engine evaluates the row inside the same logic as the card

>> Step 2: Use it as a Visual-level filter

---DAX---

Is Opening = 1

---DAX---

Now:

• Card count 

• Table rows 

• Cross-filtering 

• Slicers 

=================================================================
Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
Linkedin: https://www.linkedin.com/in/jaywantthorat/
Join #MissionPowerBIBharat = https://shorturl.at/5ViW9
#MissionPowerBIBharat
LIVE with Jaywant Thorat from 10 Jan 2026
8 Days | 8 Sessions | 1 hr daily | 100% Free

[Olufemi7]

Hello @Taher28,

Card measure (`Opening Count`):
Uses `CALCULATE + ALL()`, which overrides the Created Date slicer and correctly counts backlog items.
• Row-level flag (`Is Opening`):
Evaluates each row in the current filter context. Since slicers already removed rows, the flag never returns `1`.

 The mismatch is because measures can override filters with `CALCULATE + ALL()`, but row-level flags cannot unless you explicitly apply the same override.

 Correct Pattern

You have two reliable options:

1. Calculated Column Approach (Recommended)
Create a column that marks backlog rows once per row:IsOpeningRow =
VAR UserStartDate =
CALCULATE ( MIN ( 'Consolidated Grants'[Created Date] ), ALL ( 'Consolidated Grants' ) )
VAR SelectedYear = YEAR ( UserStartDate )
VAR ForcedEndDate = DATE ( SelectedYear, 12, 31 )
RETURN
IF (
'Consolidated Grants'[Created Date] < UserStartDate
&& 'Consolidated Grants'[Agreement Signed Date.] >= UserStartDate
&& 'Consolidated Grants'[Agreement Signed Date.] <= ForcedEndDate,
1,
0
)
• Filter the table on `IsOpeningRow = 1`.
• The card still uses your measure for counts.

2. Measure-Based Table Filter
Instead of `SELECTEDVALUE`, apply the same `CALCULATE + ALL()` logic directly:OpeningTableFilter =
CALCULATE (
COUNTROWS ( 'Consolidated Grants' ),
ALL ( 'Consolidated Grants'[Created Date] ),
'Consolidated Grants'[Created Date] < UserStartDate,
'Consolidated Grants'[Agreement Signed Date.] >= UserStartDate,
'Consolidated Grants'[Agreement Signed Date.] <= ForcedEndDate
)
• Apply as a visual filter (`> 0`) on the table.
• This ensures the table inherits the same override as the card.

ALL function (DAX): Returns all rows in a table or all values in a column, ignoring filters—used to clear filters in `CALCULATE`.
https://learn.microsoft.com/en-us/dax/all-function-dax A
• SELECTEDVALUE function (DAX): Returns the single value in the current context, or an alternate result—commonly used in row-level contexts but still subject to current filters.
https://learn.microsoft.com/en-us/dax/selectedvalue-function-dax B
• ALLSELECTED function (DAX): Keeps external filters while clearing row/column filters—useful for visual totals; included here to clarify context behavior differences vs `ALL`.
https://learn.microsoft.com/en-us/dax/allselected-function-dax C

[FreemanZ]

hi @Taher28 ,

Here is what i see:

Root cause:

The "is opening" measure will always return 0, because RowCreatedDate < UserStartDate

always returns FALSE, as whenever you select any date they become equal.

Proposal:

Use disconnected table columns to feed the date slicer.

[FBergamaschi]

Hi @Taher28 

thanks for the clear post

I am a bit unsure on your SELECTEDVALUE logic. That function returns a single value or BLANK(). The blank value is returned if the columns has more than one value visible or if the only value is BLANK(). So if you see BLANK() I am not sure that means that the date is really empty, that depends on the filters applied. But I have no way to go deep in this as I do not know the model

That said,

if you apply a measure as a filter, the measure is calculated for each row of the matrix, based on the columns you grouped into the rows section of the matrix

Can you pleease show in an image how the table / matrix visual is settled ? We need to see the cloumns you grouped into the matrix as they will become part of the evaluation of the 1 or 0 or your filter measure

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI