Power BI Question

Question

I have a table from a SQL server. When I want to see specific data via a visual based on a name I select, it self-filters certain things. My table is called WidgetHistory. The column I want to pull data from is CurrentStatusID. I need to see all widgets at status ID 23.

My current formula is:

CALCULATE (

COUNTROWS(WidgetHistory), WidgetHistory [CurrentStatusID] = 23 )

The problem is it self-filters data from another column within the table named Modifiedby. If I select a name in the drop down, it shows all widgets assigned to and modified by the person selected. If any of the widgets were modified by anyone else, they don’t appear in the count in the visual.

Example. I select John Smith in my drop down. The card visual shows 396 widgets with a current status of 23. When I go to Table view, there are 398 widgets with a current status of 23 assigned to him. 396 were assigned to and modified by John Smith. The other two were assigned to John Smith but modified by Jane Doe. This happens when I select others too.

I have tried:

CALCULATE (

COUNTROWS(WidgetHistory), WidgetHistory [CurrentStatusID] = 23, ALL(WidgetHistory[Modifiedby]))

I have replaced ALL with ALL SELECTED and REMOVEFILTERS but the visual still shows 396.

Any help getting the visual to show all 398 would be very appreciated.

Answer 1

As far as I can tell, this is an expected Power BI filter context behavior: your slicer on Name (or AssignedTo) is coming from the same table as ModifiedBy, so when you select John Smith, Power BI applies a row-level filter to WidgetHistory. By the time your measure runs, the rows modified by Jane Doe are already gone, and ALL(WidgetHistory[Modifiedby]) can’t bring them back because removing a column filter does not undo a filter that removed rows via another column in the same table.

In other words, this is not a “ModifiedBy filter” problem; it’s a table-level row filter problem.

To fix it, you should explicitly tell Power BI to ignore all filters on the WidgetHistory table except the one you care about (CurrentStatusID = 23 and assignment logic), or reconstruct the filter context using ALLEXCEPT or REMOVEFILTERS at the table level.

Try the following:

Widgets at Status 23 :=
CALCULATE (
    COUNTROWS ( WidgetHistory ),
    WidgetHistory[CurrentStatusID] = 23,
    REMOVEFILTERS ( WidgetHistory[ModifiedBy] )
)

If that still returns 396, it means the slicer is not filtering ModifiedBy directly, but filtering another column in WidgetHistory (for example AssignedTo or Name), which still removes those rows before the measure evaluates.

In that case, you should remove all table filters except the assignment column you actually want respected:

Widgets at Status 23 :=
CALCULATE (
    COUNTROWS ( WidgetHistory ),
    WidgetHistory[CurrentStatusID] = 23,
    ALLEXCEPT (
        WidgetHistory,
        WidgetHistory[AssignedTo]
    )
)

This tells Power BI: “Respect who the widget is assigned to, but ignore who modified it and any other columns in this table.”

If your slicer is on a separate Users table (recommended model design), then the solution becomes:

Widgets at Status 23 :=
CALCULATE (
    COUNTROWS ( WidgetHistory ),
    WidgetHistory[CurrentStatusID] = 23,
    REMOVEFILTERS ( WidgetHistory[ModifiedBy] )
)

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

Thank you all for your answers. Turns out it was a relationship issue